make 1 record with Union statement

5 Jan 2006 6:20 PM

Susanna

Hi there,
I've made a join query:
SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM QTYORDER',
         A.QTYONHND AS 'QTYONHND'
    FROM IV00102 A LEFT JOIN
         IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
   WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD

UNION

SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
QTYORDER',
         0 AS 'QTYONHND'
    FROM POP10110
   WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
GROUP BY ITEMDESC, ITEMNMBR

the result looks like this:

ITEMDESC    ITEMNMBR    SUM QTYORDER    QTYONHND
Proactiv (ITEM)                                        S.NL.543
          0    -477
Proactiv (ITEM)                                        S.NL.543
          6000    0

what I want is to produce 1 record which will look like this:

ITEMDESC    ITEMNMBR    SUM QTYORDER    QTYONHND
Proactiv (ITEM)                                        S.NL.543
          6000    -477

any suggestions on how to do this?

--
Thanks in advance,
Susanna

5 Jan 2006 6:32 PM

Anith Sen

Use the UNION-ed query as a derived table & use aggregate functions on the
outer query. i.e :

SELECT MAX( itemdesc ) AS "item_desc",
MAX( itemnbr ) AS "item_nbr",
....
FROM ( < your query with UNION > ) D

--
Anith

5 Jan 2006 9:52 PM

Louis Davidson

What are you doing here? It could either be the max, or adding. I am
assuming that it doesn't matter because the 0 values actually mean not
applicable in your main query.

> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM)                                    S.NL.543
>      0 -477
> Proactiv (ITEM)                                    S.NL.543
>      6000 0

Like anith says, you can just do a group, or possibly something like this,
assuming that you are actually putting out one row per ITEMMBR (if not the
union isn't going to work well either)

SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, A.QTYONHND AS
'QTYONHND',
                             (SELECT SUM(QTYORDER) AS 'SUM QTYORDER'
                             FROM POP10110
                             WHERE POLNESTA IN (2) AND ITEMNMBR =
A.ITEMNMBR) as 'QTYORDER'

FROM IV00102 A
        LEFT JOIN IV00101 B
               ON A.ITEMNMBR = B.ITEMNMBR
WHERE A.RCRDTYPE IN (2)
    AND A.LOCNCODE IN ('SALES')
    AND A.ITEMNMBR = 'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD

Do you have some issues with poor data quality? I notice that ITEMDESC
comes from an outer join (which makes me wonder what that table is for) and
what the uniqueness is on. In your second query it seems to be itemnmber.
If it is, this should work (or something close.)

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Show quote

"Susanna" <Susa***@discussions.microsoft.com> wrote in message
news:5D04D744-C679-487D-85F9-8AFBC2D3A844@microsoft.com...
> Hi there,
> I've made a join query:
> SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM
> QTYORDER',
>         A.QTYONHND AS 'QTYONHND'
>    FROM IV00102 A LEFT JOIN
>         IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
>   WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
> 'S.NL.543'
> GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
> A.ATYALLOC, A.QTYSOLD
>
> UNION
>
> SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
> QTYORDER',
>         0 AS 'QTYONHND'
>    FROM POP10110
>   WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
> GROUP BY ITEMDESC, ITEMNMBR
>
> the result looks like this:
>
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM)                                    S.NL.543
>      0 -477
> Proactiv (ITEM)                                    S.NL.543
>      6000 0
>
> what I want is to produce 1 record which will look like this:
>
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM)                                    S.NL.543
>      6000 -477
>
> any suggestions on how to do this?
>
> --
> Thanks in advance,
> Susanna