Solved

# Group by totals on union SQL 2005

Posted on 2011-05-11
Medium Priority
195 Views
How do I restructure this query so the totals are based on the union and not on the individual portions of the union? Right now if the same part exists in both halves of the union I get one total for each half. What I need is a total that combines both halves.
``````select sl.itemnmbr,sl.itemdesc,sum(sl.qtytoinv)
from sop10200 sl
inner join sop10100 sh on sl.soptype=sh.soptype and sl.sopnumbe=sh.sopnumbe
inner join iv00101 iv on sl.itemnmbr = iv.itemnmbr
where sl.soptype=3 and iv.itmclscd='WERMA'
group by sl.itemnmbr,sl.itemdesc
union all
select sl.itemnmbr,sl.itemdesc,sum(sl.qtytoinv)
from sop30300 sl
inner join sop30200 sh on sl.soptype=sh.soptype and sl.sopnumbe=sh.sopnumbe
inner join iv00101 iv on sl.itemnmbr = iv.itemnmbr
where sl.soptype=3 and iv.itmclscd='WERMA'
group by sl.itemnmbr,sl.itemdesc
order by sl.itemnmbr
``````
0
Question by:rwheeler23
• 3
• 2

LVL 33

Accepted Solution

knightEknight earned 1600 total points
ID: 35738686
select itemnmbr, itemdesc, sum(sl.qtytoinv)
from (
select sl.itemnmbr,sl.itemdesc,sum(sl.qtytoinv) as qtytoinv
from sop10200 sl
inner join sop10100 sh on sl.soptype=sh.soptype and sl.sopnumbe=sh.sopnumbe
inner join iv00101 iv on sl.itemnmbr = iv.itemnmbr
where sl.soptype=3 and iv.itmclscd='WERMA'
group by sl.itemnmbr,sl.itemdesc
union all
select sl.itemnmbr,sl.itemdesc,sum(sl.qtytoinv)
from sop30300 sl
inner join sop30200 sh on sl.soptype=sh.soptype and sl.sopnumbe=sh.sopnumbe
inner join iv00101 iv on sl.itemnmbr = iv.itemnmbr
where sl.soptype=3 and iv.itmclscd='WERMA'
group by sl.itemnmbr,sl.itemdesc
) v
group by itemnmbr, itemdesc
order by itemnmbr
0

LVL 33

Expert Comment

ID: 35738707
--perhaps easier:

select itemnmbr, itemdesc, sum(sl.qtytoinv)
from (
select sl.itemnmbr,sl.itemdesc,sl.qtytoinv
from sop10200 sl
inner join sop10100 sh on sl.soptype=sh.soptype and sl.sopnumbe=sh.sopnumbe
inner join iv00101 iv on sl.itemnmbr = iv.itemnmbr
where sl.soptype=3 and iv.itmclscd='WERMA'
union all
select sl.itemnmbr,sl.itemdesc,sl.qtytoinv
from sop30300 sl
inner join sop30200 sh on sl.soptype=sh.soptype and sl.sopnumbe=sh.sopnumbe
inner join iv00101 iv on sl.itemnmbr = iv.itemnmbr
where sl.soptype=3 and iv.itmclscd='WERMA'
) v
group by itemnmbr, itemdesc
order by itemnmbr
0

LVL 42

Assisted Solution

dqmq earned 400 total points
ID: 35739347
I forgo the points, but it in the last answer, please remove the "sl." from
"sum(sl.qtytoinv)"  in the first line
0

LVL 33

Expert Comment

ID: 35739358
Yes, thanks -- good catch.  The first line of both posts above should be:

select itemnmbr, itemdesc, sum(qtytoinv)
0

Author Comment

ID: 35740521
You folks are just the greatest resource I have ever encountered. It is so great to see information shared so promptly.

Thank you both for taking the time to respond.
0

Author Closing Comment

ID: 35740529
This site has been invaluable in providing solutions to questions I have posted.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.