Counting two values from the same table

[kid_epicurus]

I have a table of orders. Each order has a status, one being "Shipped". The end goal is to take the data and push it into a chart showing total orders, shipped orders, and grouped by month/year. I can get the count of the orders and group them by month/year, but I'm not sure how to also include orders with the status of "Shipped". Any ideas? (The code I have now is counting the orders into a column called Orders and Shipped, but I know it's not actually grabbing shipped orders for the Shipped column. I just have it there for now so I could move on with populating a chart while I work on fixing the query.)

SELECT COUNT(o.ROW_ID) AS Orders, COUNT(o2.ROW_ID) AS Shipped, RIGHT(CONVERT(VARCHAR(8), o.STATUS_DT, 3), 5) AS Date, MONTH(o.STATUS_DT) AS Month, YEAR(o.STATUS_DT) AS YearFROM dbo.S_ORDER AS o LEFT OUTER JOIN dbo.S_ORDER AS o2 ON o.ROW_ID = o2.ROW_IDWHERE (o.CREATED > CONVERT(DATETIME, '2011-04-01 00:00:00', 102)) AND (o.CREATED < CONVERT(DATETIME, '2012-05-1 00:00:00', 102))GROUP BY RIGHT(CONVERT(VARCHAR(8), o.STATUS_DT, 3), 5), MONTH(o.STATUS_DT), YEAR(o.STATUS_DT)ORDER BY Year, Month, Date DESC

[justsomeguy]

I'm not sure what you're doing with the join there, you're doing a left join on the same table and then a count, I don't see where you're specifying whether or not the order has shipped. You're not specifying that the status needs to be "Shipped", for example, you're just counting all rows in the table. You may need to do the join where you join on a temporary table that includes the row ID and a 1 if the status is "shipped", and then do a sum instead of a count.

SELECT COUNT(o.ROW_ID) AS Orders, SUM(o2.shipped) AS Shipped, ...FROM dbo.S_ORDER AS o LEFT OUTER JOIN (  SELECT ROW_ID, 1 AS `shipped` FROM dbo.S_ORDER WHERE status='Shipped') AS o2 ON o.ROW_ID = o2.ROW_IDWHERE ...

[kid_epicurus]

Correct. I'm not specifying any "Shipped" just yet because it's not working properly and I just needed to get the columns I needed for now to get the chart working. I wasn't sure how to do a count on two different things (total orders and shipped orders) within the same table, so I resorted to a join on itself as the solution. Probably not the best approach, I admit. Here's the raw columns I'm referencing, if it helps. And here is what I'm wanting to output, except obviously the Shipping numbers will vary once I am able to count both total orders and shipped orders for each month/year.

[justsomeguy]

Try the join like I showed, where it joins on a temporary table with just the ID column to join on and a 1 if the order was shipped. You need to use SUM instead of COUNT because COUNT will still return the total number of rows, but SUM will give the number of rows that had a 1 for the shipped column in the temp table.

[kid_epicurus]

That did the trick! Thanks, justsomeguy.