Jump to content

Counting two values from the same table


Recommended Posts

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

Link to post
Share on other sites

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 ...

Link to post
Share on other sites

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. grmlH.png 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. cJrmZ.png

Link to post
Share on other sites

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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...