brain too small

jx12345 · #1 (**permalink**) 08-23-11, 05:11

I'm afraid my brain is too small to figure out how to do the following in sql and i was hoping someone with a large brain might lend me some of theirs....

i have a table that contains sales data as follows:

order_no, date, order_status
1, 2011-08-22, 1
2, 2011-08-22, 2
3, 2011-08-23, 2
4, 2011-08-23, 1
5, 2011-08-23, 1

(order_status > 1 is a completed order)

and i want to be able to produce a summary as follows:

date, completed_orders, failed_orders
2011-08-22, 1, 1
2011-08-23, 1, 2

i tried to achieve this with count & grouping but failed and then read about pivoting but found my brain rejected it... any help much appreciated.

thanks.

r937 · #2 (**permalink**) 08-23-11, 05:29

Code:

SELECT `date`
     , COUNT(CASE WHEN order_status > 1
                  THEN 'humpty' END) AS completed_orders
     , COUNT(CASE WHEN order_status = 1
                  THEN 'dumpty' END) AS failed_orders
  FROM daTable
GROUP
    BY `date`

jx12345 · #3 (**permalink**) 08-23-11, 05:44

thanks rudy, your brain is of an enviable size...

a couple of quick queries, if you have the time..

whats the point of the 'then' part of the query and why is it needed?

thanks again,

jim

r937 · #4 (**permalink**) 08-23-11, 05:51

a CASE expression requires a THEN clause in order to have something to evaluate to

i use 'humpty' and 'dumpty' but they really could be any values

the point is, if the CASE condition is false, the expression evaluates to the ELSE value, and if the ELSE value is not explicitly given, it defaults to NULL

and as you know, aggregate functions like COUNT ignore NULLs

so the COUNT functions only count the non-NULL values, 'humpty' and 'dumpty'

jx12345 · #5 (**permalink**) 08-23-11, 06:12

many thanks rudy... i appreciate you taking the time...

ta

j

jx12345 · #6 (**permalink**) 08-23-11, 06:33

i'd like to also dispaly percentages of the completed / failed orders (never satisfied!!)

i was hoping something similar to the following would work:
SELECT substr(created, 1,10) as date
, COUNT(CASE WHEN order_status > 1
THEN 'humpty' END) AS completed_orders
, COUNT(CASE WHEN order_status = 1
THEN 'dumpty' END) AS failed_orders
, (completed_orders / (completed_orders + failed_orders) *100) as prct_sucess

FROM orders
GROUP
BY `date`

but i receive the following:

Unknown column 'completed_orders' in 'field list'

i imagine the case parts are evaluated after so i cannot access them - could anyone point me in the right direction?

r937 · #7 (**permalink**) 08-23-11, 07:32

why are you taking a substring of a date? what's that column's actual datatype?

jx12345 · #8 (**permalink**) 08-23-11, 08:06

oh, its a time stamp so its got the date & time - 2011-08-23 11:34:12 or whatever but i dont want to group them by second! just by day so i pulled out the first 10 characters to just get the date

r937 · #9 (**permalink**) 08-23-11, 08:26

what you want is the DATE() function, not SUBSTR()

Code:

SELECT date_created
     , completed_orders
     , failed_orders
     , 100.0 * completed_orders / 
              (completed_orders + failed_orders) AS prct_sucess
  FROM ( SELECT DATE(created) AS date_created
              , COUNT(CASE WHEN order_status > 1
                           THEN 'humpty' END) AS completed_orders
              , COUNT(CASE WHEN order_status = 1
                           THEN 'dumpty' END) AS failed_orders
           FROM orders
         GROUP
             BY date_created ) AS t

jx12345 · #10 (**permalink**) 08-23-11, 08:39

beautiful.

Internet Services

Web Development

Digital Marketing

Consumer Tech