MySQL Forums
Forum List  »  Newbie

Problem with Rollup syntax
Posted by: angel rivero
Date: February 22, 2014 04:09AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Problem with Rollup syntax
February 22, 2014 04:09AM
February 24, 2014 11:02AM
February 25, 2014 07:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.