I have a problem with the ROLLUP, I have rows with null values, and the ROLLUP also returns null, how do I difference between the null values of the ROLLUP and the null values of the row?
The null in the rows exist because the column (group_name) is associated with a left join.
Here is my query:
SELECT gr.info,
HOUR(cdr.calldate) AS hour,
DATE(cdr.calldate) AS date,
COUNT(DISTINCT cdr.uniqueid) AS count,
pl.number,
IFNULL(ugr.group_name, "") AS group_name
FROM cdr
INNER JOIN callinfo AS ci
ON ci.uniqueid = cdr.uniqueid
LEFT JOIN users AS usr
ON usr.username = ci.agent
LEFT JOIN groups AS ugr
ON ugr.group_id = usr.group_id
INNER JOIN pstnline AS pl
ON ci.line = pl.number
INNER JOIN hunt_line AS gri
ON gri.pstnline_id = pl.pstnline_id
INNER JOIN hunt AS gr
ON gri.hunt_number = gr.number
WHERE cdr.calldate >='2012-12-01 00:00'
AND cdr.calldate <='2013-01-24 10:45'
GROUP BY group_name WITH ROLLUP
I see that in SQL Server exist a function called GROUPING, but in MySql doesn't exist, how can i achieve the same effect?
I think you can also do this in the query that you have, by changing the group by argument to:
group by ifnull(ugr.group_name, '')
Now, blanks will indicate NULLs from the outer join and NULLs will indicate rollup.