Results 1 to 10 of 10

Thread: brain too small

  1. #1
    Join Date
    Apr 2010
    Posts
    53

    Unanswered: brain too small

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    53
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2010
    Posts
    53
    many thanks rudy... i appreciate you taking the time...

    ta

    j

  6. #6
    Join Date
    Apr 2010
    Posts
    53
    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?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why are you taking a substring of a date? what's that column's actual datatype?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2010
    Posts
    53
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2010
    Posts
    53
    beautiful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •