20

I have two basic queries which I need to sum the totals of: 

Select hours, sum(hours) FROM table WHERE name='xxx' and Description='Worked'
Select hours2, sum(hours2) FROM table WHERE name='xxx' and Description2='Worked'

I've tried UNION and it will get me the totals of each query but it will not combine them.

Table setup is:

  • ID
  • name
  • hours
  • description
  • hours2
  • description2

I need to correlate hours to description and hours2 to description2 which is why I have the two different queries. I need to sum the totals of hours and hours2.

This Guy
  • 203
  • 1
  • 2
  • 5

2 Answers2

39

First of all, you missed group by, so even though mysql doesn't complain about it, you hours and hours2 values are meaningless. Secondly, you the result of UNION can be put in derived subquery, so you will have the desired total :

SELECT SUM(hr) FROM
(
  Select sum(hours) as hr FROM table WHERE name='xxx' and Description='Worked'
  UNION ALL
  Select sum(hours2) as hr FROM table WHERE name='xxx' and Description2='Worked'
)a
a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • Good or bad, MySQL won't complain about the lack of GROUP BY by design: http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html – OMG Ponies Jul 13 '11 at 21:01
  • @OMG Ponies: I didn't say it's a bad feature (sometimes it's very useful), I just pointed out that in such context the values of non-aggregate are meaningless. – a1ex07 Jul 13 '11 at 21:04
  • 2
    Thank you very much. I'm very new to this so the structure is probably way off. What is the 'a' for at the end? I had tried that exact query before but with the 'a'. – This Guy Jul 13 '11 at 21:06
  • 3
    @This Guy: The `a` at the end is a table alias -- most databases require one is defined for a derived table/inline view like you see in Alex's answer – OMG Ponies Jul 13 '11 at 21:10
2

You would need to place your union into a subquery:

SELECT  Hours,
        SUM(Hours) AS Hours,
        SUM(Hours2) AS Hours2
FROM    (   SELECT  Hours,
                    SUM(Hours) AS Hours,
                    0 AS Hours2
            FROM    Table
            WHERE   Name = 'xxx'
            AND     Description = 'Worked'
            GROUP BY Hours
            UNION ALL
            SELECT  Hours2,
                    0 AS Hours,
                    SUM(Hours2) AS Hours
            FROM    Table
            WHERE   Name = 'xxx'
            AND     Description2 = 'Worked'
            GROUP BY Hours2
        ) t
GROUP BY Hours;
GarethD
  • 68,045
  • 10
  • 83
  • 123