Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2012
    Posts
    9

    Unanswered: Multiple counts on a single line, by day

    Here's my base query:

    SELECT day(`dateTime`) as day, `status`, count(*) FROM table WHERE `dateTime` >= '2012-XX-XX 00:00:00'
    GROUP BY day, `status`

    Output~

    day status count(*)
    1 sts1 194
    1 sts2 367
    1 sts3 72
    2...
    2..
    2.

    There are 3 possible statuses(statii?). The output of 3 lines is fine when looking at 1 day, but it can get kind of ugly when you have 30 days. I want the output to look like this but for the life of me I can't figure it out.

    day status count(*) status count(*) status count(*)
    1 sts1 194 sts2 367 sts3 72
    2 sts1 201 sts2 376 sts3 91
    3..
    4.

    I know this has got to be something simple. Please help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT DAY(`dateTime`) as theday
         , COUNT(CASE WHEN status=1 THEN 'ok' END) AS count_status1
         , COUNT(CASE WHEN status=2 THEN 'ok' END) AS count_status2
         , COUNT(CASE WHEN status=3 THEN 'ok' END) AS count_status3
      FROM daTable 
     WHERE `dateTime` >= '2012-XX-XX 00:00:00'
    GROUP 
        BY theday
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2012
    Posts
    9
    Thank you. I've re-written this so that I have the appropriate table and fields, but it constantly returns an empty result

    When I pull using my base query I get data. Could this be failing to pull data because I'm using phpMyAdmin?

    Again, thank you for your assistance!

    SELECT DAY(`dateTime`) as day
    , COUNT(CASE WHEN status='paid' THEN 'ok' END) AS paid
    , COUNT(CASE WHEN status='pending' THEN 'ok' END) AS pending
    , COUNT(CASE WHEN status='failed' THEN 'ok' END) AS failed
    FROM `table`
    WHERE `dateTime` >= '2012-08-10 00:00:00'
    GROUP BY day

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bdatws View Post
    Could this be failing to pull data because I'm using phpMyAdmin?
    not at all

    please dump the table design and a few rows of relevant data, so we can test this ourselves
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2012
    Posts
    9
    Thank you, r937!

    I sussed it. The only thing I needed to change was

    THEN 'ok' END

    to

    THEN 1 END

    This is solved!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bdatws View Post
    The only thing I needed to change was

    THEN 'ok' END

    to

    THEN 1 END

    This is solved!
    dude, that change had no effect whatsoever on how COUNT works

    ~any~ non-NULL value will work -- 'ok', 1, 'frabdabazingzong', 9e37, ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2012
    Posts
    9
    All I know is that 'ok' did not work when 1 did. The former was valid, no errors returned, though an empty result was. Why?, I do not know. Magic is a fine explanation by me. Either way, it's fixed and I thank you for your assistance.

  8. #8
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Might it have been because 'ok' was alpha and 1 was numeric?

    Just a guess . . .

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... an empty result was.
    What means empty result?
    COUNT(...) always return a number, even if no data it returns 0.


    MySQL :: MySQL 5.5 Reference Manual :: 12.16.1 GROUP BY (Aggregate) Functions
    •COUNT(expr)

    Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

    COUNT() returns 0 if there were no matching rows.
    Last edited by tonkuma; 08-14-12 at 15:37. Reason: Add a reference

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by papadi View Post
    Might it have been because 'ok' was alpha and 1 was numeric?
    nope

    all that matters is that the thing that you're counting is null or not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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