Tuesday, March 15, 2011

Group Union

I have the following query that always give me two rows. I want to group them to one row using tbluser.userid, is it possible to group a union?

SELECT
SUM(tblfooditem.calories)
FROM tblfooditem
INNER JOIN tbluser ON tblfooditem.userid = tbluser.userid
WHERE tblfooditem.userid=?userid AND tblfooditem.date=?date
GROUP BY tbluser.userid
UNION
SELECT
SUM(tbladdedmealitem.calories)
FROM tbladdedmeal
INNER JOIN tbluser ON tbladdedmeal.userid = tbluser.userid
WHERE tbladdedmeal.userid=?userid AND tbladdedmeal.date=?date
GROUP BY tbluser.userid;
From stackoverflow
  • It's possible.

    select sum(s) from (
    SELECT
        SUM(tblfooditem.calories) S,tbl.userid
        FROM tblfooditem
        INNER JOIN tbluser ON tblfooditem.userid = tbluser.userid
        WHERE tblfooditem.userid=?userid AND tblfooditem.date=?date
        GROUP BY tbluser.userid
        UNION
        SELECT
        SUM(tbladdedmealitem.calories) S,tbl.userid
        FROM tbladdedmeal
        INNER JOIN tbluser ON tbladdedmeal.userid = tbluser.userid
        WHERE tbladdedmeal.userid=?userid AND tbladdedmeal.date=?date
        GROUP BY tbluser.userid
    ) Q
    group by Q.userid
    

0 comments:

Post a Comment