Hi guys, hope in your help I'm very confused ...
This is mysql query join and output:
mysql> SELECT
IFNULL(RDT, 'Tot') AS `Unity`,
User AS `User`,
myDate AS `myDate`,
Energy AS `Energy`
FROM
(
SELECT
query2.RDT AS RDT,
query2.User AS User,
query2.myDate AS myDate,
ROUND(
Energy,
3
) AS Energy
FROM
`tbl_q1` query1
JOIN `tbl_q2` query2 ON query1.User = query2.User
GROUP BY
query2.User
) AS subQ
WHERE
subQ.User IS NOT NULL
ORDER BY
subQ.RDT DESC;
+---------+-----------+------------+--------+
| Unity | User | myDate | Energy |
+---------+-----------+------------+--------+
| UOT RDT | 405424401 | 2014-01-14 | 0,001 |
| UOT RDT | 418594683 | 2014-02-04 | 0,083 |
| UOT RDT | 443638024 | 2014-01-13 | 0,008 |
| UOT RDT | 444497548 | 2014-01-13 | 0,068 |
| RAS RDT | 980185591 | 2014-01-08 | 0,03 |
| RAS RDT | 980185605 | 2014-02-19 | 0,078 |
| RAS RDT | 980188051 | 2014-01-09 | 0,003 |
| RAS RDT | 980193888 | 2014-02-06 | 0,084 |
| RAS RDT | 983493416 | 2014-02-06 | 0,117 |
| RAS RDT | 986837507 | 2014-01-08 | 0,185 |
| RAS RDT | 990911240 | 2014-01-09 | 0,002 |
| RAS RDT | 991833005 | 2014-02-10 | 0,016 |
| RAS RDT | 991860258 | 2014-02-06 | 0,004 |
| RAS RDT | 994481525 | 2014-01-17 | 0,008 |
| RAS RDT | 998783691 | 2014-02-10 | 0,003 |
| MAL RDT | 613011374 | 2014-01-31 | 0,003 |
| MAL RDT | 613177183 | 2014-01-09 | 0,001 |
| MAL RDT | 614895918 | 2014-01-14 | 0,018 |
| MAL RDT | 615607231 | 2014-02-12 | 0,153 |
| MAL RDT | 679387693 | 2014-01-13 | 0,001 |
| MAL RDT | 679723936 | 2014-02-07 | 0,046 |
| MAL RDT | 688373361 | 2014-02-03 | 0,317 |
| MAL RDT | 689670989 | 2014-02-13 | 0,018 |
| MAL RDT | 600948601 | 2014-01-15 | 0,014 |
| MAL RDT | 602766632 | 2014-01-16 | 0,063 |
| MAL RDT | 605844855 | 2014-01-08 | 0,825 |
| MAL RDT | 605845967 | 2014-01-30 | 0,031 |
| MAL RDT | 608032851 | 2014-01-07 | 0,073 |
| MAL RDT | 611973144 | 2014-02-14 | 0,539 |
| PMC RDT | 800250277 | 2014-02-04 | 0,095 |
| PMC RDT | 802741375 | 2014-01-27 | 1,68 |
| PMC RDT | 876412624 | 2014-01-10 | 0,156 |
+---------+-----------+------------+--------+
32 rows in set
The SUM of column energy is 4,723 but if launch this query with rollup:
mysql> SELECT
IFNULL(query2.RDY, 'Tot') AS `RDT`,
query2.Energy AS `Energy`
FROM
`tbl_Q1` query1
JOIN `tbl_Q2` query2 ON query1.User = query2.User
GROUP BY
query2.RDT WITH ROLLUP;
+---------+---------+
| RDT | Energy |
+---------+---------+
| PMC RDT | 0,09512 |
| MAL RDT | 0,0179 |
| RAS RDT | 0,18513 |
| UOT RDT | 0,08252 |
| Tot | 0,08252 |
+---------+---------+
5 rows in set
The SUM of column Energy is wrong and it's the same of SUM energy of UOT RDT... can you help me?
Thank you in advance.