CODEWITHSUNDEEP
sqljoinsubquery
egg
egg

Reputation: 373

joining a subquery with aggregate data

I am having trouble combined two queries, using join/subquery.

My first query is pulling sales totals (this example pulls just one product):

SELECT
DAB020.ARTNR,
SUM(DAB020.RE_WERT) as "Total Euros",
COUNT(DISTINCT DAB020.ANUMMER) as "Number of Orders"
FROM "DAB020.ADT" DAB020
WHERE DAB020.RE_DATUM >= { d '2021-01-01' } AND DAB020.ARTNR = '123456'
GROUP BY DAB020.ARTNR

example results:

ARTNR   Total VK €  Auftrag Nr
123456  999999,50   60

works fine, giving for one article, the total sales this year + the unique number of order numbers.

My second query (coming from a different table), shows the number of sales offers made:

Select
COUNT(DISTINCT left (DAB055.APNUMMER,6)) as "# offers"
FROM "DAB055.ADT" DAB055
WHERE (DAB055.BUCH_DATUM>={d '2021-01-01'}) // YYYY.MM.DD
AND DAB055.ARTNR = '123456'

example results:

ARTNR   # offers
123456  120

also works fine, shows for the article number, the count of unqiue offer numbers.

Now I want to append the offer results, onto the end of the sales results:

SELECT
DAB020.ARTNR,
SUM(DAB020.RE_WERT) as "Total Euro",
COUNT(DISTINCT DAB020.ANUMMER) as"Number of Orders",

left join (
  Select
  COUNT(DISTINCT left (DAB055.APNUMMER,6)) as "# offers"
  FROM "DAB055.ADT" DAB055
  WHERE (DAB055.BUCH_DATUM>={d '2021-01-01'}) // YYYY.MM.DD
  ) as results on DAB055.ARTNR = DAB020.ARTNR

FROM "DAB020.ADT" DAB020
WHERE DAB020.RE_DATUM >= { d '2021-01-01' } AND DAB020.ARTNR = '969226'
GROUP BY DAB020.ARTNR

desired results:

ARTNR   Total VK €  Auftrag Nr  # offers
123456  999999,50   60          120

but it doesn't work of course. The link between the two tables is DAB055.ARTNR and DAB020.ARTNR, but I am not sure how to get the syntax correct.

Can somone clarify my mistake? Thanks!

EDIT: just a note - im using only one article here for testing. For the final query, it will be a few hundred.

Upvotes: 0

Views: 132

Answers (1)

DRapp
DRapp

Reputation: 48139

Your query is only doing a single product, you can just do each query as a from alias. Since each returns a single row, it will be 1:1 and pull values directly. I still have the key ID in each respectively to accommodate final join. However, if you wanted other ARTNR values, or ALL ARTNR for the date time range in question, just remove the single ARTNR from each subquery respectively in the where clauses. Then you can get all offers with orders and euro counts/totals.

SELECT
        Q1.ARTNR,
        Q1.TotalEuro "Total Euro",
        Q1.NumberOfOrders "Number of Orders",
        Q2.NumberOffers "# Offers"
    from
        ( SELECT
                DAB020.ARTNR,
                SUM(DAB020.RE_WERT) as TotalEuro,
                COUNT(DISTINCT DAB020.ANUMMER) as NumberofOrders,
            FROM 
                "DAB020.ADT" DAB020
            WHERE 
                    DAB020.RE_DATUM >= { d '2021-01-01' } 
                AND DAB020.ARTNR = '969226'
            GROUP BY 
                DAB020.ARTNR ) Q1
    JOIN
        ( Select
                DAB055.ARTNR,
                COUNT(DISTINCT left (DAB055.APNUMMER,6)) as NumberOffers
            FROM 
                "DAB055.ADT" DAB055
            WHERE 
                    DAB055.BUCH_DATUM >= { d '2021-01-01'}
                AND DAB055.ARTNR = '969226'
            GROUP BY
                DAB055.ARTNR ) Q2
         on Q1.ARTNR = Q2.ARTNR

Upvotes: 1

Related Questions