Summing multiple fields in single query

[bd528]

Hi have the following query :-

SELECT t1.QuoteID, t1.Supply_Num, t2.Cost AS Stage1Cost
FROM qryCML as t1 LEFT JOIN tblCommissionNew as t2 ON t1.QuoteID = t2.Quote_ID
WHERE (((t2.[stage])=1))
GROUP BY t1.QuoteID, t1.Supply_Num, t2.Cost;

This results in 3 columns, one of which is a total where the stage = 1.

Is it possible, in the same query, to have a forth column showing the total where the stage = 2?

Thanks

 

[Ranman256]

you can sum as many fields as you want.

 

[Minty]

you can sum as many fields as you want.

Well technically that's true - but not with different criteria in the same query. You would have to use a sub query for the other result set.

 

[plog]

I question your SQL:

1. Why is this an aggregate query (use GROUP BY) when you use no aggregate functions? You say one of the columns is a Total field, but it doesn't SUM, so is it really a total?

2. When you apply criteria to a field in the LEFT JOIN datasource, you have effectively made it an INNER JOIN:

FROM qryCML as t1 LEFT JOIN tblCommissionNew as t2 ON t1.QuoteID = t2.Quote_ID
WHERE (((t2.[stage])=1))

That code is an INNER JOIN, meaning you are not showing all the records from qryCML, instead you are showing only the matching records between the two data sources. Don't know if that matters, but that's what's occuring.

Perhaps you can demonstrate what you want with data. Provide 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show the data you expect to be ultimately returned when you feed it the A data.

 

[bd528]

I question your SQL:

1. Why is this an aggregate query (use GROUP BY) when you use no aggregate functions? You say one of the columns is a Total field, but it doesn't SUM, so is it really a total?

2. When you apply criteria to a field in the LEFT JOIN datasource, you have effectively made it an INNER JOIN:

FROM qryCML as t1 LEFT JOIN tblCommissionNew as t2 ON t1.QuoteID = t2.Quote_ID
WHERE (((t2.[stage])=1))

That code is an INNER JOIN, meaning you are not showing all the records from qryCML, instead you are showing only the matching records between the two data sources. Don't know if that matters, but that's what's occuring.

Perhaps you can demonstrate what you want with data. Provide 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show the data you expect to be ultimately returned when you feed it the A data.

Yes, all good points - I had a few issues with my provided query - apologies.

Basically, I need to return every record from qryCML, and the corresponding data from tblCommissionNew. qryCML & tblCommissionNew are related by QuoteID/Quote_ID.

The relevant fields in tblCommissionNew are Stage; Cost; Quote_ID

Some sample data
qryCML
Quote ID
001
002
003

tblCommissionNew
Stage / Cost / Quote ID
1 / 1.44 / 001
2 / 2.87 / 001
1 / 5.66 / 002

Expected results
QuoteID/ Stage1Cost / Stage2Cost
001 / 1.44 / 2.87
002 / 5.66 / 0
003 / 0 / 0

Apologies again.

 

[plog]

If there are only 2 stages, you can do this by moving the WHERE criteria you have to the SELECT. However, for more than 2 stages I recommend you move to a cross-tab query (https://support.office.com/en-us/ar...ab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8).

Based on the data you provided, this SQL will deliver what you want:

SELECT qryCML.[Quote ID], SUM(Iif(Stage=1, Cost, 0)) AS Stage1Cost, SUM(Iif(Stage=2, Cost, 0)) AS Stage2Cost
FROM qryCML
LEFT JOIN tblCommissionNew ON tblComissionNew.[Quote ID] = qryCML.[Quote ID]
GROUP BY qryCML.[QUOTE ID];

 

[Minty]

Create a new query. Add in tblCommissionNew and qryCML. Join them on the QuoteID.
Change the query type to cross tab. Have a play with the values and row / column headings.

Eve will be your Auntie.


edit. Damn that Plog and his faster fingers....

 

[bd528]

Based on the data you provided, this SQL will deliver what you want:

SELECT qryCML.[Quote ID], SUM(Iif(Stage=1, Cost, 0)) AS Stage1Cost, SUM(Iif(Stage=2, Cost, 0)) AS Stage2Cost
FROM qryCML
LEFT JOIN tblCommissionNew ON tblComissionNew.[Quote ID] = qryCML.[Quote ID]
GROUP BY qryCML.[QUOTE ID];

This worked. Thank you very much.