SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How can I get a subtotal and total in the same query?

    Hi there, I hope your help.

    I get a subtotal and total in the same query and tried this solution, but I need this other output, can you help me?
    thank you.
    Code:
    +--------------------+------------------+---------------+-----------+--------------+-----------------+----------------------+
    | Day_of_the_event_s | DescriptionEven  | number_events | tot_CC80  | lines_tb_tni | tot_clients_int | Duration_of_theEvent |
    +--------------------+------------------+---------------+-----------+--------------+-----------------+----------------------+
    | 2013-01-08         | NUXXX            |             2 | 99.15     | 2            | 36              | 134                  |
    | 2013-01-08         | CAXXX            |             5 | 96.29     | 5            | 188             | 88                   |
    | 2013-01-08         | TIXXX            |             2 | 85.30     | 2            | 64              | 143                  |
    | 2013-01-08         | AVXXX            |             1 | 76.00     | 1            | 151             | 76                   |
    | 2013-01-08         | LUXXX            |             1 | 52.00     | 1            | 38              | 52                   |
    | 2013-01-08         | subtotal         |            11 | 408.74    | 36           | 1637            | 2867                 |
    | 2013-01-07         | TEXXX            |             4 | 837.94    | 4            | 181             | 472                  |
    | 2013-01-07         | LAXXX            |             3 | 824.00    | 3            | 276             | 59                   |
    | 2013-01-07         | ATXXX            |             1 | 82.00     | 1            | 77              | 82                   |
    | 2013-01-07         | CAXXX            |            16 | 3069.45   | 16           | 1029            | 1660                 |
    | 2013-01-07         | CSXXX            |            15 | 2254.22   | 15           | 841             | 1165                 |
    | 2013-01-07         | subtotal         |            40 | 7067.61   | 206          | 12145           | 19831                |
    |                    | total            |            51 | 7476.35   | 242          | 13782           | 22698                |
    +--------------------+------------------+---------------+-----------+--------------+-----------------+----------------------+
    Code:
    mysql> SELECT
    	Day_of_the_event_s,
            DescriptionEven,
    	number_events,
    	COALESCE (tot_CC80, 'Tot') tot_CC80,
    	lines_tb_tni,
    	tot_clients_int,
    	Duration_of_theEvent
    FROM
    	(
    		SELECT
    			Day_of_the_event_s,
                            DescriptionEven,
    			COUNT(*) AS number_events,
    			SUM(cc80) AS tot_CC80,
    			SUM(lines_tb_tni) AS lines_tb_tni,
    			SUM(
    				tot_clients_int
    			) AS tot_clients_int,
    			SUM(Duration_of_theEvent) AS Duration_of_theEvent
    		FROM
    			tbl_cc80
    		GROUP BY
    			Day_of_the_event_s,
    			Description_event WITH ROLLUP
    	) qw
    ORDER BY
    	Day_of_the_event_s DESC,
    	tot_CC80 DESC;
    +--------------------+------------------+---------------+-----------+--------------+-----------------+----------------------+
    | Day_of_the_event_s | DescriptionEven  | number_events | tot_CC80  | lines_tb_tni | tot_clients_int | Duration_of_theEvent |
    +--------------------+------------------+---------------+-----------+--------------+-----------------+----------------------+
    | 2013-01-08         | NUXXX            |             2 | 99.15     | 2            | 36              | 134                  |
    | 2013-01-08         | CAXXX            |             5 | 96.29     | 5            | 188             | 88                   |
    | 2013-01-08         | TIXXX            |             2 | 85.30     | 2            | 64              | 143                  |
    | 2013-01-08         | AVXXX            |             1 | 76.00     | 1            | 151             | 76                   |
    | 2013-01-08         | NULL             |            11 | 408.74    | 36           | 1637            | 2867                 |
    | 2013-01-08         | LUXXX            |             1 | 52.00     | 1            | 38              | 52                   |
    | 2013-01-07         | TEXXX            |             4 | 837.94    | 4            | 181             | 472                  |
    | 2013-01-07         | LAXXX            |             3 | 824.00    | 3            | 276             | 59                   |
    | 2013-01-07         | ATXXX            |             1 | 82.00     | 1            | 77              | 82                   |
    | 2013-01-07         | NULL             |            40 | 7067.61   | 206          | 12145           | 19831                |
    | 2013-01-07         | CAXXX            |            16 | 3069.45   | 16           | 1029            | 1660                 |
    | 2013-01-07         | CSXXX            |            15 | 2254.22   | 15           | 841             | 1165                 |
    +--------------------+------------------+---------------+-----------+--------------+-----------------+----------------------+
    12 rows in set

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you have done this before, it requires COALESCE

    i've shown you how a couple of times, you will just have to go back to your previous threads and look it up (or get someone else to do it for you again)

    in fact, you're using COALESCE on tot_cc80 (but you shouldn't, because it will never be null)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, thank you I understand

    I changed in my query this line:
    Code:
    DescriptionEven,
    to:
    Code:
    	COALESCE (
    		DescriptionEven,
    		'GRAND TOTAL'
    	) AS DescriptionEven,
    And this line:
    Code:
    COALESCE (tot_CC80, 'Tot') tot_CC80,
    to:
    Code:
    tot_CC80 AS `print_name`,
    and I added in the ORDER BY clause:
    Code:
    ORDER BY
    	Day_of_the_event_s DESC,
    	tot_CC80 DESC,
    	CASE
    WHEN `print_name` = 'GRAND TOTAL' THEN
    	0
    ELSE
    	1
    END,
     `print_name`;
    Now the output is:
    Code:
    +--------------------+------------------+---------------+
    | Day_of_the_event_s | DescriptionEven  | number_events |
    +--------------------+------------------+---------------+
    | 2013-01-08         | GRAND TOTAL      |            13 |
    | 2013-01-08         | FIXXX            |             8 |
    | 2013-01-08         | POXXX            |             5 |
    | 2013-01-07         | GRAND TOTAL      |            31 |
    | 2013-01-07         | CAXXX            |            16 |
    | 2013-01-07         | CSXXX            |            15 |
    | NULL               | GRAND TOTAL      |            44 |
    +--------------------+------------------+---------------+
    but it's correct more so or not?
    Code:
    +--------------------+------------------+---------------+
    | Day_of_the_event_s | DescriptionEven  | number_events |
    +--------------------+------------------+---------------+
    | 2013-01-08         | FIXXX            |             8 |
    | 2013-01-08         | POXXX            |             5 |
    | 2013-01-08         | GRAND TOTAL      |            13 |
    | 2013-01-07         | CAXXX            |            16 |
    | 2013-01-07         | CSXXX            |            15 |
    | 2013-01-07         | GRAND TOTAL      |            31 |
    | NULL               | GRAND TOTAL      |            44 |
    +--------------------+------------------+---------------+

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    but it's correct more so or not?
    what do you think? are you happy with the results?

    if not, you need to change your ORDER BY criteria
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, thanks a lot!
    Code:
    ......
    ORDER BY
    	Day_of_the_event_s DESC,
    	CASE
    WHEN `print_name` = 'GRAND TOTAL' THEN
    	0
    ELSE
    	1
    END,
     `print_name`;
    
    +--------------------+------------------+---------------+
    | Day_of_the_event_s | DescriptionEven  | number_events |
    +--------------------+------------------+---------------+
    | 2013-01-08         | POXXX            |             5 |
    | 2013-01-08         | FIXXX            |             8 |
    | 2013-01-08         | GRAND TOTAL      |            13 |
    | 2013-01-07         | CSXXX            |            15 |
    | 2013-01-07         | CAXXX            |            16 |
    | 2013-01-07         | GRAND TOTAL      |            31 |
    | NULL               | GRAND TOTAL      |            44 |
    +--------------------+------------------+---------------+
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's certainly progress

    however, usually there may be multiple subtotals, but only one "grand total"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's certainly progress
    thank you very much.
    however, usually there may be multiple subtotals, but only one "grand total"
    Of course in fact, inserting a new clause WHERE in the query, this is the new output with two lines "grand total", why? :
    Code:
    mysql> SELECT
    	Day_of_the_event_s,
    	COALESCE (
    		DescriptionEven,
    		'GRAND TOTAL'
    	) AS DescriptionEven,
    	number_events,
    	tot_CC80 AS tot_CC80
    FROM
    	(
    		SELECT
    			Day_of_the_event_s,
    			DescriptionEven,
    			COUNT(*) AS number_events,
     			SUM(CC80) AS tot_CC80
    
    		FROM
    			tbl_CC80
    		WHERE
    			1
    		AND LEFT (Zn, 2) = 'WQ'
    		AND Day_of_the_event_s = DATE_SUB(
    			CURRENT_DATE (),
    			INTERVAL 2 DAY
    		)
    		GROUP BY
    			Day_of_the_event_s,
    			DescriptionEven WITH ROLLUP
    	) qw
    ORDER BY
    	Day_of_the_event_s DESC,
    	CASE
    WHEN tot_CC80 = 'GRAND TOTAL' THEN
    	0
    ELSE
    	1
    END,
     tot_CC80 DESC;
    
    +--------------------+-----------------+---------------+----------+
    | Day_of_the_event_s | DescriptionEven | number_events | tot_CC80 |
    +--------------------+-----------------+---------------+----------+
    | 2013-01-08         | GRAND TOTAL     |            14 |  2415.91 |
    | 2013-01-08         | FIXXX           |             8 |  1691.91 |
    | 2013-01-08         | GRXXX           |             2 |   230.00 |
    | 2013-01-08         | ARXXX           |             1 |   186.00 |
    | 2013-01-08         | TEXXX           |             1 |   145.00 |
    | 2013-01-08         | SIXXX           |             1 |   111.00 |
    | 2013-01-08         | LUXXX           |             1 |    52.00 |
    | NULL               | GRAND TOTAL     |            14 |  2415.91 |
    +--------------------+-----------------+---------------+----------+
    8 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  8. #8
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I find the problem, the cause is the clause GROUP BY.
    Replaced this line:
    Code:
    		GROUP BY
    			Day_of_the_event_s,
    			DescriptionEven WITH ROLLUP) qw
    ORDER BY
    	Day_of_the_event_s DESC,
    	CASE
    WHEN tot_CC80 = 'GRAND TOTAL' THEN
    	0
    ELSE
    	1
    END,
     tot_CC80 DESC;
    to:
    Code:
    		GROUP BY
    			DescriptionEven WITH ROLLUP) qw
    ORDER BY
    	Day_of_the_event_s DESC,
    	CASE
    WHEN tot_CC80 = 'GRAND TOTAL' THEN
    	0
    ELSE
    	1
    END,
     tot_CC80 ASC;
    new output:
    Code:
    +--------------------+-----------------+---------------+----------+
    | Day_of_the_event_s | DescriptionEven | number_events | tot_CC80 |
    +--------------------+-----------------+---------------+----------+
    | 2013-01-08         | LUXXX           |             1 |    52.00 |
    | 2013-01-08         | SIXXX           |             1 |   111.00 |
    | 2013-01-08         | TEXXX           |             1 |   145.00 |
    | 2013-01-08         | ARXXX           |             1 |   186.00 |
    | 2013-01-08         | GRXXX           |             2 |   230.00 |
    | 2013-01-08         | FIXXX           |             8 |  1691.91 |
    | NULL               | GRAND TOTAL     |            14 |  2415.91 |
    +--------------------+-----------------+---------------+----------+
    8 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    so you don't want subtotals for each day?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    so you don't want subtotals for each day?
    are two different output's:
    1. subtotals for each day and grand total;
    2. grand total for single day;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

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