Too long combined queries 2

[cumap]

Hi all,

Can this query be any shorter or better, please help

sql = "SELECT " _
& " (SELECT SUM(moneySaleTotal) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 1 " _
& "	AND idUser <> 4 " _
& "	AND dtAddDate LIKE '" & gMonth & "%" & gYear & "%' " _
& "	AND custType = 'Reseller') AS tMonthlySumReseller, " _
& " (SELECT SUM(moneySaleTotal) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 1 " _
& "	AND idUser <> 4 " _
& "	AND dtAddDate LIKE '%" & gYear & "%' " _
& "	AND custType = 'Reseller') AS tYearlySumReseller, " _
& " (SELECT SUM(moneySaleTotal) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 1 " _
& "	AND idUser <> 4 " _
& "	AND dtAddDate LIKE '" & gMonth & "%" & gYear & "%' " _
& "	AND custType = 'Manufacturer') AS tMonthlySumManu, " _
& " (SELECT SUM(moneySaleTotal) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 1 " _
& "	AND idUser <> 4 " _
& "	AND dtAddDate LIKE '%" & gYear & "%' " _
& "	AND custType = 'Manufacturer') AS tYearlySumManu, " _
& " (SELECT SUM(moneySaleTotal) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 1 " _
& "	AND idUser <> 4 " _
& "	AND dtAddDate LIKE '" & gMonth & "%" & gYear & "%' " _
& "	AND custType = 'Government') AS tMonthlySumGovt, " _
& " (SELECT SUM(moneySaleTotal) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 1 " _
& "	AND idUser <> 4 " _
& "	AND dtAddDate LIKE '%" & gYear & "%' " _
& "	AND custType = 'Government') AS tYearlySumGovt, " _
& " (SELECT SUM(moneySaleTotal) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 1 " _
& "	AND idUser <> 4 " _
& "	AND dtAddDate LIKE '" & gMonth & "%" & gYear & "%' " _
& "	AND custType = 'Other') AS tMonthlySumOther, " _
& " (SELECT SUM(moneySaleTotal) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 1 " _
& "	AND idUser <> 4 " _
& "	AND dtAddDate LIKE '%" & gYear & "%' " _
& "	AND custType = 'Other') AS tYearlySumOther, " _
& " (SELECT SUM(numQty) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 2 " _
& "	AND idUser <> 4) AS tSumKitQuotes, " _
& " (SELECT SUM(numQty) FROM tblCPBPS " _
& "	WHERE idCPBPScat = 1 " _
& "	AND idUser <> 4) AS tSumKitSold, " _
& " (SELECT COUNT(*) FROM tblCPBPS " _
& "	WHERE idProdCat = 3 " _
& "	AND idUser <> 4) AS tSumKitDemo, " _
& " (SELECT COUNT(*) FROM tblCPBPS " _
& "	WHERE bolChkNew = '1' " _
& "	AND idUser <> 4) AS tSumNewResellers " _
& "FROM tblCPBPS"

Thanks!

 

[gmmastros]

Can this query be any shorter or better

Yes, this can be a lot better. It won't necessarily be much shorter, but shorter code isn't really all that important. It's important that your code return the correct values, and that it's fast.

The first thing I noticed is that there is only one table involved here. I also noticed that ALL of the subqueries have a common part (and idUser <> 4). Since this is common to all subqueries, we can move that part to the end (using the new structure I am about to show you).

Next, your query involves a lot of SUMS, and a couple COUNTs. These are considered aggregate functions. All aggregate functions ignore nulls. We can use this to our advantage. By using a CASE/WHEN expression within the sum & count aggregates, we can get them to return the same values without needing to use a subquery to do it.

Ex:

SELECT  Sum([!]Case When idCPBPScat = 1  
                      AND dtAddDate LIKE ' & gMonth & % & gYear & %'  
                      AND custType = 'Reseller'
                 Then MoneySaleTotal
                 End[/!]) AS tMonthlySumReseller,  
        SUM(Case When idCPBPScat = 1  
                      AND dtAddDate LIKE '% & gYear & %'  
                      AND custType = 'Reseller' 
                 Then moneySaleTotal
                 End) AS tYearlySumReseller,  
        SUM(Case When idCPBPScat = 1  
                      AND dtAddDate LIKE ' & gMonth & % & gYear & %'  
                      AND custType = 'Manufacturer' 
                 Then moneySaleTotal 
                 End) AS tMonthlySumManu,
        Count(Case When idProdCat = 3 Then 1 End) As tSumKitDemo,  
        Count(Case When bolChkNew = '1' Then 1 End) As tSumNewResellers  
FROM  tblCPBPS
Where idUser <> 4

Notice how the where clause criteria in your first subquery became a Case/When condition. Think of it this way.... if your data does not satisfy the case expression, the value NULL will be used. Sum ignores NULL, so the moneySaleTotal from that row will be ignored.

In your original query (that utilized subqueries), the SQL Engine needed to 'go to the table' a dozen times. Basically, each calculation was performed separately. With this new query, the SQL engine makes just one pass through the table calculating each row simultaneously. I would expect this new query to be at least a dozen times faster (if not more).

Lastly, notice that I only did a couple of your columns. The ones I did not do are similar enough to the ones that I did for you to get the idea. If you have any problems, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cumap]

Huuray!!!!!!!!!!!!!!
Thank George, you have always been my answers all along. And your resolution is exactly what I'm looking for "just one pass through the table". This is the first time I met such a CASE WHEN in sql, which show how little I know about it.

keep up the good work!

 

[cumap]

A little adding the previous one... I modified the query into here:

sql = "SELECT " _
& "	tblCPBPS.idUser, " _
& "	SUM(CASE WHEN CAST(FLOOR(CAST(tblCPBPS.dtAddDate AS FLOAT))AS DATETIME) = '" & today & "' " _
& "	THEN tblCPBPS.numTotalPt END) AS tDailyPt, " _
& "	SUM(CASE WHEN tblCPBPS.dtAddDate LIKE '" & gMonth & "%" & gYear & "%' " _
& "	THEN tblCPBPS.numTotalPt END) AS tMonthlyPt, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '" & gMonth & "%" & gYear & "%' AND tblCPBPS.custType='Reseller' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tMonthlyResellerSold, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '%" & gYear & "%' AND tblCPBPS.custType='Reseller' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tYearlyResellerSold, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '" & gMonth & "%" & gYear & "%' AND tblCPBPS.custType='Manufacturer' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tMonthlyManuSold, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '%" & gYear & "%' AND tblCPBPS.custType='Manufacturer' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tYearlyManuSold, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '" & gMonth & "%" & gYear & "%' AND tblCPBPS.custType='Government' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tMonthlyGovtSold, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '%" & gYear & "%' AND tblCPBPS.custType='Government' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tYearlyGovtSold, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '" & gMonth & "%" & gYear & "%' AND tblCPBPS.custType='Other' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tMonthlyOtherSold, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '%" & gYear & "%' AND tblCPBPS.custType='Other' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tYearlyOtherSold, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '" & gMonth & "%" & gYear & "%' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tMonthlySold, " _
& "	SUM(CASE WHEN tblCPBPS.idCPBPScat=1 AND tblCPBPS.dtAddDate LIKE '%" & gYear & "%' " _
& "	THEN tblCPBPS.moneySaleTotal END) AS tYearlySold, " _
& "     SUM(CASE WHEN tblCPBPS.idCPBPScat=2 THEN tblCPBPS.numQty END) AS tKitQuotes, " _
& "     SUM(CASE WHEN tblCPBPS.idCPBPScat=1 THEN tblCPBPS.numQty END) AS tKitSold, " _
& "	COUNT(CASE WHEN tblCPBPS.idProdCat=3 THEN 1 END) AS tKitDemo, " _
& "     COUNT(CASE WHEN tblCPBPS.bolChkNew='1' THEN 1 END) AS countNewResellers " _
& "FROM tblCPBPS " _
& "INNER JOIN tblUsers " _
& "	ON tblCPBPS.idUser = tblUsers.UserID " _
& "WHERE tblUsers.Saleman=1 " _
& " AND tblUsers.Active=1 " _
& "GROUP BY tblCPBPS.idUser " _
& "ORDER BY tblCPBPS.idUser"

And this works just fine, problem is, I'd like to have a UserName from the tblUsers table (not just the tblCPBPS.idUser.)

I tried adding tblUsers.UserName into the query and I right away got an ANONYMOUS error from Godaddy indicating problem, that's all. So now, I cannot tell why the query doesn't work, so maybe someone can tell me why or help me fix it.

Thanks!

 

[cumap]

I added this to the SELECT of the query and it works as well

(SELECT UserName FROM tblUsers WHERE UserID = tblCPBPS.idUser) AS sellername

is it efficient to do so? and why can't I just call tblUsers.UserName just like any INNER JOIN query I've seen and done elsewhere?

 

[gmmastros]

Without an error message, it will be VERY difficult to find the error.

I suggest you check your spelling, making sure you got table names and column names correct (tblUsers.Saleman, Should that be sale[!]s[/!]man).

Also, if you want to return the UserName in the select clause, you'll need to add it to the group by.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cumap]

Perfect!!!!!!!!!!!!!!!!!

Why do I keep forgetting about the GROUP BY?

Thanks for pointing that out! Good eye

 

[cumap]

Sorry for cheating another star from you for answering 2 questions of mine.

 

[gmmastros]

I appreciate the "thank you", but I could care less about stars. I'm glad my advice helped you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom