How to do this in group by query without lost performance ?

[hvsummer]

Hi everyone,

After "Decade" from beginning, I have to ask this question:

how to show up zero volume in group by query w/o losing performance.

I have 2 tables like this:

(Raw data) Table1 = {CID, Name, ItemID, BDate, Quantity, Price}
(List Client) Table2 = {CustID, CustName}

ok, my current SQL to show up 0 quantity Client below:

SELECT CustID, CustName, iif(Month(BDate)=11,Month(BDate),11) as BMonth, Sum(iif(Month(BDate)=11,Quatity,0)) as Total-MTD
 
FROM Table2 LEFT JOINT Table1 ON Table2.CustID = Table1.CID
 
WHERE iif(Month(BDate)=11,Month(BDate),11) = 11
 
GROUP BY CustID, CustName, Month(BDate)
 
ORDER BY CustID, Month(BDate);
 

if I use this 2nd SQL below, all 0-Volume-Client will be disappeared

SELECT CustID, CustName, Month(BDate) as BMonth, Sum(Quatity) as Total-MTD
 
FROM Table2 LEFT JOINT Table1 ON Table2.CustID = Table1.CID
 
WHERE Month(BDate) = 11
 
GROUP BY CustID, CustName, Month(BDate)
 
ORDER BY CustID, Month(BDate);
 

how to show those Client without using 1st SQL or losing performance ?

Edit:
I have idea using Subquery like this:

SELECT CustID, CustName, Month(BDate) as BMonth, Sum(Quatity) as Total-MTD
 
FROM Table2 LEFT JOINT Table1 ON Table2.CustID = Table1.CID
 
WHERE Month(BDate) = 11
 
GROUP BY CustID, CustName, Month(BDate)
 
ORDER BY CustID, Month(BDate)
 
UNION ALL
 
SELECT CustID, CustName, 11 as BMonth, 0 as Total-MTD
 
FROM Table2 LEFT JOINT Table1 ON Table2.CustID = Table1.CID
 
WHERE NOT EXIST
(SELECT CustID, CustName, Month(BDate) as BMonth, Sum(Quatity) as Total-MTD
 
FROM Table2 LEFT JOINT Table1 ON Table2.CustID = Table1.CID
 
WHERE Month(BDate) = 11
 
GROUP BY CustID, CustName, Month(BDate)
 
ORDER BY CustID, Month(BDate));
 
 

[Rabbit]

It's impossible to not lose some performance when you need to make a query more complicated.

That being said, there are different ways of writing the same query that lose less performance. But you won't ever find a way of making a simple query more complicated without losing some performance.

A couple of other ways of writing the query would be to use the Nz function instead of the IIf function that you used in the first code block. You could also try an Or Is Null instead. In your third block of code, for the bottom half of the union all, instead of a subquery, you could move the criteria into a join and return only the nulls on the outer table.

Try all 3 different methods and see which one is the fastest.

[hvsummer]

0.0 I did say 2nd method not qualify haha

I want to sum the volume and show up that record even volume = 0
2nd SQL will ignore any 0 volume (whether does exit or does not)..

i already test 1st method and 3rd method, not really improve anything ==

[Rabbit]

0.0 I did say 2nd method not qualify haha

I didn't say anything about your second method. I know it's not what you need.

I want to sum the volume and show up that record even volume = 0
2nd SQL will ignore any 0 volume (whether does exit or does not)..

Yes, I know what you're looking for. You described this clearly enough in the first post.

i already test 1st method and 3rd method, not really improve anything ==

You mean you tested the 1st and 3rd methods in your first post. But you haven't tried the 3 variations that I suggested in my post. The methods you posted are not the same as the 3 variations I suggested.