Board index » delphi » SUM 2 Tables in one single Query!

SUM 2 Tables in one single Query!

I have a problem sum 2 fileds from diffrent tables in one single query.

Where are 3 tables involved:
'DebGroup' including the fields: 'DebGroup', 'Name'
'Invoices' includning the fileds 'DebGroup', 'ProdGroup', 'Qty', 'Cost',
'Sales'
'Claims' including the fields 'DebGroup', 'ProdGroup', 'Qty', 'Cost'

What I want is first to calculate the sum from the field 'Sales' in the
table 'Invoices'. This could (a bit overelaborated) look like:

SELECT D.DebGroup, SUM(I.Sales) Invoiced_Amount
FROM DebGroup D, Invoices I
WHERE D.DebGroup = I.DebGroup
GROUP BY D.DebGroup

And then the same calculateing for the claim cost:

SELECT D.DebGroup, SUM(C.Cost) Claimed_Amount
FROM DebGroup D, Claims C
WHERE D.DebGroup = C.DebGroup
GROUP BY D.DebGroup

But now to the question! How would be the correct way to put this two
queries into one single query? The result should look something like:

DebGroup    Invoiced_Amount    Claimed_Amount
SE                24510                     2978
FI                 18567                     1754
----

Niklas Larsson

 

Re:SUM 2 Tables in one single Query!


    Hi,
    This must be a right query:

SELECT D.DebGroup, SUM(I.Sales) Invoiced_Amount, SUM(C.Cost) Claimed_Amount
FROM Invoices I
LEFT JOIN DebGroup D ON I.DebGroup = D.DebGroup
RIGHT JOIN Claims C ON D.DebGroup = C.DebGroup
GROUP BY D.DebGroup

Re:SUM 2 Tables in one single Query!


Thanks Venelin for your reply but unfortunatly it dont seems to work.

Look at this example with my data. First an overview:

SELECT *
FROM DebGroup

gives...

DebGroup     Name
FI                  Finland
SE                 Sweden
NO                Norway

SELECT *
FROM Invoices

gives (a bit slimed)...

DebGroup     ProdGroup     Sales
SE                 KK                200
SE                 ET                 300
SE                 KK               100
SE                 ET                 200
NO                KK               200
NO                ET                200
NO                KK               300
FI                  KK               400
FI                  ET                400
FI                  ET                200

SELECT *
FROM Claims

gives (also a bit slimed)...

DebGroup     ProdGroup     Cost
SE                 KK                3456
SE                 KK                2312
SE                 ET                 300
FI                  KK               1234
NO                ET                456

Tables sumed one by one. First Invoices:

SELECT D.DebGroup, SUM(I.Sales) Invoiced_Amount
FROM DebGroup D, Invoices I
WHERE D.DebGroup = I.DebGroup
GROUP BY D.DebGroup

Result:
DebGroup     Invoiced_Amount
FI                 1000
NO               700
SE                 800

Then the claims:

SELECT D.DebGroup, SUM(C.Cost) Claimed_Amount
FROM DebGroup D, Claims C
WHERE D.DebGroup = C.DebGroup
GROUP BY D.DebGroup

Result:
DebGroup     Claimed_Amount
FI                  1234
NO                456
SE                 6068

The result of my asked query should consequently look like:

DebGroup     Invoiced_Amount      Claimed_Amount
FI                  1000                         1234
NO                700                           456
SE                  800                           6068

So far so good. Now to your suggestion:

SELECT D.DebGroup, SUM(I.Sales) Invoiced_Amount, SUM(C.Cost) Claimed_Amount
FROM Invoices I
LEFT JOIN DebGroup D ON I.DebGroup = D.DebGroup
RIGHT JOIN Claims C ON D.DebGroup = C.DebGroup
GROUP BY D.DebGroup

When I try it I get the result:

DebGroup     Invoiced_Amount      Claimed_Amount
FI                  1000                         3702
NO                700                           1368
SE                  2400                         24272

Look at the result for DebGroup 'FI' and 'NO', its correct! Unfortunatly
the rest is wrong. It seems like the JOINs makes to much rows to sum, just
like many other exemples I tried. Do you get the same result as I and do you
have any suggestion how to alter your first query.

Thanks
Niklas Larsson

"Venelin Dimitroff" <veneli...@yahoo.com> skrev i meddelandet
news:3d2574a3$1_2@dnews...

Quote
>     Hi,
>     This must be a right query:

> SELECT D.DebGroup, SUM(I.Sales) Invoiced_Amount, SUM(C.Cost)
Claimed_Amount
> FROM Invoices I
> LEFT JOIN DebGroup D ON I.DebGroup = D.DebGroup
> RIGHT JOIN Claims C ON D.DebGroup = C.DebGroup
> GROUP BY D.DebGroup

Re:SUM 2 Tables in one single Query!


Try 2 left joins instead so you're{*word*154} your deb's and cred's off the
invoices lines (which presumably is a complete set of keys so will alway
join onto Invoice, hence should work -- notice I've changed the join
condition from Venelin's suggestion):

SELECT D.DebGroup, SUM(I.Sales) Invoiced_Amount, SUM(C.Cost) Claimed_Amount
FROM Invoices I
LEFT JOIN DebGroup D ON I.DebGroup = D.DebGroup
LEFT JOIN Claims C ON I.DebGroup = C.DebGroup
GROUP BY D.DebGroup

HTH

Walter Prins

Re:SUM 2 Tables in one single Query!


Hi Walter!

Thanks for your reply.

Unfortunatly your suggestion not work either. The result is the exact same
as I get from Venelins query!

You, and Venelin, are most welcome to try again!

Niklas

"Walter Prins" <wprins.nos...@ananzi.co.za> skrev i meddelandet
news:3d28adc6_1@dnews...

Quote
> Try 2 left joins instead so you're{*word*154} your deb's and cred's off the
> invoices lines (which presumably is a complete set of keys so will alway
> join onto Invoice, hence should work -- notice I've changed the join
> condition from Venelin's suggestion):

> SELECT D.DebGroup, SUM(I.Sales) Invoiced_Amount, SUM(C.Cost)
Claimed_Amount
> FROM Invoices I
> LEFT JOIN DebGroup D ON I.DebGroup = D.DebGroup
> LEFT JOIN Claims C ON I.DebGroup = C.DebGroup
> GROUP BY D.DebGroup

> HTH

> Walter Prins

Re:SUM 2 Tables in one single Query!


After reading the other queries, you're probably looking for something like
this...

SELECT
  DebGroup,
  SUM(Invoiced_Amount) Invoiced_Amount,
  SUM(Claimed_Amount) Claimed_Amount
FROM ( -- Subselects...
SELECT
  D.DebGroup,
  SUM(I.Sales) Invoiced_Amount,
  0 Claimed_Amount
FROM
  DebGroup D,
  Invoices I
WHERE
  D.DebGroup = I.DebGroup
GROUP BY
  D.DebGroup
UNION ALL SELECT
  D.DebGroup,
  0 Invoiced_Amount,
  SUM(C.Cost) Claimed_Amount
FROM
  DebGroup D, Claims C
WHERE
  D.DebGroup = C.DebGroup
GROUP BY
  D.DebGroup
) -- End subselects
GROUP BY
  DebGroup

Other Threads