Solved

# Group by totals on union SQL 2005

Posted on 2011-05-11
176 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

LVL 33

Accepted Solution

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

--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

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

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

select itemnmbr, itemdesc, sum(qtytoinv)
0

Author Comment

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

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

## Featured Post

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even withinâ€¦
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!