question

eddd83 avatar image
eddd83 asked

Grouping subquery?

so this query works for a specific userID. ![alt text][1] However, when I run for the entire data warehourse (same query, but eliminate the "customer_profile.userID = 2022765" lines), it sums the entire database for the depositSum value for each userID instead of calculating the depositSum value for each individual userID. I can't use the group by within the subquery because it would return multiple columns. ![alt text][2] Help? [1]: /storage/temp/3570-untitled1.jpg [2]: /storage/temp/3571-untitled2.jpg
sqlsumsubquery
untitled1.jpg (95.9 KiB)
untitled2.jpg (175.5 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Instead of a picture can you post the actual query. The reason this isn't working is because you are using subqueries for your sums. Not sure why you are doing that to be honest. It should be just a column in the main query, not a subquery.
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
It looks like the query works for a single user by accident, only because the specified user has only one deposit and withdrawal. You are using inner join of 3 tables, so if the customer has multiple deposits and/or withdrawals, you will get multiple rows for the same person. Even though you use the subqueries for deposits and withdrawals, they will still not prevent the multiple rows for the same userID in case if the user has multiple deposits and withdrawals.Try this query instead, it should work. select cp.userID, d.depositSum, w.Wsum, cp.amountGamingPlayerBalancecurrent PlayerBalance, d.depositSum * 0.09 Psum from customer_profile cp cross apply ( select sum(amountSuccessfulDeposits) depositSum from deposit where userID = cp.userID ) d cross apply ( select sum(amountWithdrawal) Wsum from withdrawal where userID = cp.userID and withdrawalType = 'Total' ) w This query will return one row per userID (please note that there is no need for **group by** clause here). If you do not want to consider any rows where the users don't have any withdrawals with withdrawalType = 'Total' then add the where clause to the bottom of the query above reading **where w.Wsum is not null** Hope this helps, Oleg
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.