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

cms9651 · January 9, 2013, 5:56pm

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.

+--------------------+------------------+---------------+-----------+--------------+-----------------+----------------------+
| 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                |
+--------------------+------------------+---------------+-----------+--------------+-----------------+----------------------+

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

r937 · January 9, 2013, 6:59pm

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)

cms9651 · January 9, 2013, 9:22pm

ok, thank you I understand

I changed in my query this line:

DescriptionEven,

to:

	COALESCE (
		DescriptionEven,
		'GRAND TOTAL'
	) AS DescriptionEven,

And this line:

COALESCE (tot_CC80, 'Tot') tot_CC80,

to:

tot_CC80 AS `print_name`,

and I added in the ORDER BY clause:

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:

+--------------------+------------------+---------------+
| 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?

+--------------------+------------------+---------------+
| 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 |
+--------------------+------------------+---------------+

r937 · January 9, 2013, 9:30pm

what do you think? are you happy with the results?

if not, you need to change your ORDER BY criteria

cms9651 · January 9, 2013, 9:47pm

Yes, thanks a lot!


......
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 |
+--------------------+------------------+---------------+

r937 · January 9, 2013, 10:54pm

that’s certainly progress

however, usually there may be multiple subtotals, but only one “grand total”

cms9651 · January 10, 2013, 7:45am

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? :

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

cms9651 · January 10, 2013, 9:06am

I find the problem, the cause is the clause GROUP BY.
Replaced this line:

		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:

		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:

+--------------------+-----------------+---------------+----------+
| 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

r937 · January 10, 2013, 11:50am

so you don’t want subtotals for each day?

cms9651 · January 10, 2013, 12:32pm

are two different output’s:

subtotals for each day and grand total;
grand total for single day;