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

[cms9651]

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]

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]

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]

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

[cms9651]

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]

that's certainly progress

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

[cms9651]

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

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]

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]

so you don't want subtotals for each day?

[cms9651]

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;