hello everyone, I hope your help.
I can’t understand why different output between these two queries.
Can you help me?
Thanks in advance.
The correct output:
SELECT
Q1.RegisterDate,
MAX(Q1.warning) warning
FROM
(
SELECT
RegisterDate,
IF (
lcase(class) LIKE '%iso%',
repetitions,
0
) warning
FROM
tbl_m1
WHERE
elements <> 'NMT'
GROUP BY
repetitions
) Q1
GROUP BY
RegisterDate;
RegisterDate warning
2012-07-01 94
2012-07-02 98
2012-07-03 8
2012-07-04 8
2012-07-05 8
The wrong output:
CREATE TABLE TestTable (
`RegisterDate` VARCHAR (50),
`WARNING` INT
);
INSERT INTO TestTable (
`RegisterDate`,
`WARNING`
) SELECT
RegisterDate,
warning
FROM
(
SELECT
RegisterDate,
IF (
(class) LIKE '%iso%',
MAX(repetitions),
0
) warning
FROM
tbl_m1
WHERE
1
AND elements <> 'NMT'
GROUP BY
repetitions
) AS SubQs
WHERE
1
GROUP BY
RegisterDate;
SELECT
RegisterDate,
warning
FROM
(
SELECT
RegisterDate,
warning
FROM
TestTable
UNION
SELECT
RegisterDate,
SUM(warning)
FROM
TestTable
GROUP BY
(RegisterDate) WITH ROLLUP
) q;
DROP TABLE TestTable;
RegisterDate warning
2012-07-01 0
2012-07-02 0
2012-07-03 0
2012-07-04 0
2012-07-05 0
0