0

first time poster and just looking for some guidance on writing a case statement. I'd like to pull the following from a single table

total orders by week cancelled orders by week % of orders cancelled

I did some reading on case statements, but couldn't find exactly what I was looking for. I assume the case statement would be something along the lines of

"case when order = 'cancelled' THEN count orders", but I also know that's wrong, so just looking for some assistance.

Thanks in advance!

-ET

etaylor07
  • 11
  • 1
  • 1
    Can you _edit_ your post and add sample data and expected results? Also, could you tag your question specifying which DBMS you're using. That would help us give you a better answer for your question – dvo Dec 30 '19 at 18:10
  • 2
    Sample data, desired results and an appropriate database tag would all be very helpful. – Gordon Linoff Dec 30 '19 at 18:11

2 Answers2

0

The usual ways of doing a count of partial rows is to do a count query with a where clause to select which rows. Each of these can be outer joined with other similar queries.

The way to do it in one select using case is to put the case inside a sum:

SELECT SUM(case when order = 'cancelled' THEN 1 ELSE 0 END) AS cancelled, ...
karmakaze
  • 34,689
  • 1
  • 30
  • 32
0

You should probably build a nested query for this: First, build you query to get things sorted by week.

SELECT orderID, orderStatus, DATEPART(week, orderDate) as orderWeek
FROM orders

Then you can probably get a select from that as follows:

SELECT count(orderID) OVER(PARTITION BY orderWeek) AS Total
,count(orderID) OVER(PARTITION BY orderWeek, orderStatus) AS CountPerStatus
,orderWeek
FROM (SELECT orderID, orderStatus, DATEPART(week, orderDate) as orderWeek
FROM orders) a

add then finally get your totals on one row:

SELECT Total, CountPerStatus, orderWeek
FROM (
    SELECT count(orderID) OVER(PARTITION BY orderWeek) AS Total
    ,count(orderID) OVER(PARTITION BY orderWeek, orderStatus) AS CountPerStatus
    ,orderWeek
    FROM (
        SELECT orderID, orderStatus, DATEPART(week, orderDate) as orderWeek
        FROM orders
) a
) b
WHERE CountPerStatus = 'Cancelled'

This can clearly be simplified but I prefer to explicit it for better understanding. Hope it helps.

Pierre
  • 159
  • 4