CODEWITHSUNDEEP
sqlsql-server
user2441297
user2441297

Reputation: 249

Aggregate query with subquery (SUM)

I have the following query:

SELECT UserId, (
                0.099 *
                    (   
                    CASE WHEN 
                    (SELECT AcceleratedProfitPercentage FROM CustomGroups cg 
                    INNER JOIN UserCustomGroups ucg ON ucg.CustomGroupId = cg.Id
                    WHERE Packs.UserCustomGroupId = ucg.Id) 
                    IS NOT NULL THEN 
                    ((SELECT AcceleratedProfitPercentage FROM CustomGroups cg 
                    INNER JOIN UserCustomGroups ucg ON ucg.CustomGroupId = cg.Id
                    WHERE Packs.UserCustomGroupId = ucg.Id)*1.0) / (100*1.0)
                    ELSE 1
                    END
                    )
                )
                As amount
                FROM Packs WHERE Id IN (
                SELECT ap.Id FROM Packs ap JOIN Users u ON ap.UserId = u.UserId 
                WHERE ap.MoneyToReturn > ap.MoneyReturned AND 
                u.Mass LIKE '1%');

which is producing correct output. However I have no idea how to aggregate it properly. I tried to use standard GROUP BY but I get the error (Column 'Packs.UserCustomGroupId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY claus). Any ideas? Here is the output I currently get:

sql output

I want to aggregate it by UserId. Thanks in advance.

Upvotes: 0

Views: 78

Answers (2)

Stan Shaw
Stan Shaw

Reputation: 3034

Wow that is one crazy query you've got going on there.

Try this:

SELECT  UserId,
        0.099 * SUM(t.Amount) AS [Amount SUM]
FROM Packs P
JOIN Users U
    ON P.UserID = U.UserID
LEFT OUTER JOIN UserCustomGroups UCG
    ON P.UserCustomGroupID = UCG.ID
LEFT OUTER JOIN CustomGroups CG
    ON UCG.CustomGroupID = CG.ID
CROSS APPLY
    (
        SELECT CASE WHEN CG.ID IS NULL
            THEN 1
            ELSE CG.AcceleratedProfitPercentage / 100
        END AS [Amount]
    ) t
WHERE P.MoneyToReturn > P.MoneyReturned
AND U.Mass LIKE '1%'
GROUP BY UserID

First, multiplying any number by 1 is pretty pointless, yet I see it twice in your original post. I'm not sure what led to that, but it's unnecessary.

Also, using CROSS APPLY will eliminate the need for you to repeat your subquery. Granted, it's slower (since it'll run on every row returned), but I think it makes sense in this case...Using left outer joins instead of CASE - SELECT - IS NULL makes your query much more efficient and much more readable.

Next, it appears that you are attempting to SUM percentages. Not sure what kind of data you're looking to return, but perhaps AVG would be more appropriate? I can't think of any practical reason why you would be looking to do that.

Lastly, APH's answer will most certainly work (assuming your original query works), but given the obfuscation and inefficiency of your query, I would definitely rewrite it.

Please let me know if you have any questions.

Upvotes: 1

APH
APH

Reputation: 4154

The option that involves the least query-rewriting is to drop your existing query into a CTE or temp table, like so:

; with CTE as (MyQueryHere)

Select UserID, sum(amount)
from CTE
Group by UserID

Upvotes: 2

Related Questions