none
Error on using count aggregate function inside sum

    Question

  • I have the following query :

    select
            a.username, count(a.problemID) as NumberOfSubmission,
            Q_Score = sum(
            case
                when a.accepted = 0  then -2
                when count(a.accepted) > 1 then -1
              else 20
            end)
        from tblSubmission a

    I am getting the following error :

    "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

    Can anyone tell me how can I use the count aggregation function or something similar to count here?

    Thank You


    Saturday, June 14, 2014 9:48 AM

Answers

  • try this then

    SELECT [username],

    COUNT(ProblemID) AS NoOfSubmissions, SUM( CASE WHEN accepted = 1 THEN Score WHEN CountSCore > 1 THEN -1 ELSE -2 END) as TotalScore FROM ( SELECT *,count(accepted) over (partition by username) AS CountScore FROM tblSubmission s INNER JOIN tblScore sc ON sc.problemID = s.problemID )t GROUP BY [username]



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by six_sic6 Saturday, June 14, 2014 1:22 PM
    Saturday, June 14, 2014 11:07 AM
  • Ok. I think I got the issue

    Can you try this small tweak and see?

    SELECT [username],
    SUM(
      CASE 
        WHEN Accepted = 1 AND Seq = 1 THEN t.score
        WHEN Accepted = 1 AND Seq > 1  THEN -1
        WHEN Accepted = 0 THEN -2 END
    ) as TotalScore
    FROM
    (
    SELECT s.*, sc.score,
    ROW_NUMBER() OVER (PARTITION BY [username],s.problemID,Accepted ORDER BY s.username) AS Seq
    FROM tblSubmission s
    INNER JOIN tblScore sc
    ON sc.problemID = s.problemID
    )t
    GROUP BY [username]


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by six_sic6 Saturday, June 14, 2014 10:16 PM
    Saturday, June 14, 2014 7:01 PM

All replies

  • didnt you try the suggestion I gave here?

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ad0c5902-fe7b-4c04-aff6-c924bf378738/group-by-clause-based-on-condition?forum=transactsql


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by six_sic6 Saturday, June 14, 2014 10:23 AM
    • Unmarked as answer by six_sic6 Saturday, June 14, 2014 10:23 AM
    Saturday, June 14, 2014 10:14 AM
  • Anyways if you want your above query to work. tweak it as below

    select
            a.username, count(a.problemID) as NumberOfSubmission,
            sum(
            case
                when a.accepted = 0  then -2
                when count(a.accepted)over (partition by a.username) > 1 then -1
              else 20
            end) as Q_Score 
        from tblSubmission a
    group by a.username


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, June 14, 2014 10:16 AM
  • Yes I tried your suggestion and that worked partially. But now this is a different issue I am facing
    Saturday, June 14, 2014 10:23 AM
  • Yes I tried your suggestion and that worked partially. But now this is a different issue I am facing

    Did you try the above suggestion i gave?

    Also why original suggestion didnt work fully? Do you've any other conditions which you've not told us so far?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Saturday, June 14, 2014 10:34 AM
    Saturday, June 14, 2014 10:33 AM
  • It was my mistake. Actually I copied your code and did some modifications, that gave some unexpected result. Marked your answer as correct on that post :)

    Thank you.

    Saturday, June 14, 2014 10:38 AM
  • I have tried this based on the original suggestion :

    SELECT [username], [accepted], 
    
    SUM(
      CASE 
         WHEN accepted = 1 THEN Score 
         WHEN count(accepted) over (partition by username) > 1 THEN -1
         ELSE -2 
      END) as TotalScore
    
    FROM tblSubmission s
    INNER JOIN tblScore sc
    ON sc.problemID = s.problemID
    
    GROUP BY [username], [accepted]

    It's giving me the error :

    Windowed functions cannot be used in the context of another windowed function or aggregate.: SELECT [username], [accepted], SUM( CASE WHEN accepted = 1 THEN Score WHEN count(accepted) over (partition by username) > 1 THEN -2 ELSE -2 END) as TotalScore FROM tblSubmission s INNER JOIN tblScore sc ON sc.problemID = s.problemID GROUP BY [username], [accepted]

    Can you help me on this?


    • Edited by six_sic6 Saturday, June 14, 2014 11:00 AM Duplicacy
    Saturday, June 14, 2014 10:58 AM
  • try this then

    SELECT [username],

    COUNT(ProblemID) AS NoOfSubmissions, SUM( CASE WHEN accepted = 1 THEN Score WHEN CountSCore > 1 THEN -1 ELSE -2 END) as TotalScore FROM ( SELECT *,count(accepted) over (partition by username) AS CountScore FROM tblSubmission s INNER JOIN tblScore sc ON sc.problemID = s.problemID )t GROUP BY [username]



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by six_sic6 Saturday, June 14, 2014 1:22 PM
    Saturday, June 14, 2014 11:07 AM
  • Thanks for the reply. Actually I was trying something more on

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ad0c5902-fe7b-4c04-aff6-c924bf378738/group-by-clause-based-on-condition?forum=transactsql

    The score will be updated based on the following criteria :

    - For each accepted = 0, a -2 will be penalty

    - For each accepted = 1, a score will be added

    - After a first accepted = 1, then for each accepted = 1, a -1 will be penalty 

    I don't know how much i have able to describe :( .

    Saturday, June 14, 2014 1:29 PM
  • ok as per latest explanation what you need is this

    SELECT [username],
    SUM(CASE WHEN Accepted = 1 AND Seq = 1 THEN Score
    WHEN Accepted = 1 AND Seq > 1  THEN -1
    WHEN Accepted = 0 THEN -2 END) as TotalScore
    FROM
    (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY [username],problemID ORDER BY Id) AS Seq
    FROM tblSubmission s
    INNER JOIN tblScore sc
    ON sc.problemID = s.problemID
    )t
    GROUP BY [username]


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, June 14, 2014 3:42 PM
  • Thanks again. I am trying your suggestion part by part. I got some error so I did some modifications

    SELECT [username],
    SUM(
      CASE 
        WHEN Accepted = 1 AND Seq = 1 THEN t.score
        WHEN Accepted = 1 AND Seq > 1  THEN -1
        WHEN Accepted = 0 THEN -2 END
    ) as TotalScore
    FROM
    (
    SELECT s.*, sc.score,
    ROW_NUMBER() OVER (PARTITION BY [username],s.problemID ORDER BY s.username) AS Seq
    FROM tblSubmission s
    INNER JOIN tblScore sc
    ON sc.problemID = s.problemID
    )t
    GROUP BY [username]

    Sub-query in FROM clause gives the following result

    USERNAME PROBLEMID ACCEPTED SCORE SEQ
    UserA 1 1 10 1
    UserA 1 0 10 2
    UserA 1 1 10 3
    UserA 2 1 30 1
    UserB 1 0 10 1
    UserB 1 1 10 2
    UserB 2 1 30 1

    For UserB, the score would be :

    For problemID 1 :

    -> (-2) + (10)

    For problemID 2:

    -> 30.

    Final Score : 30 + 8 = 38.

    But the whole query gives something different :(

    USERNAME TOTALSCORE
    UserA 37
    UserB 27

    Saturday, June 14, 2014 5:06 PM
  • Ok. I think I got the issue

    Can you try this small tweak and see?

    SELECT [username],
    SUM(
      CASE 
        WHEN Accepted = 1 AND Seq = 1 THEN t.score
        WHEN Accepted = 1 AND Seq > 1  THEN -1
        WHEN Accepted = 0 THEN -2 END
    ) as TotalScore
    FROM
    (
    SELECT s.*, sc.score,
    ROW_NUMBER() OVER (PARTITION BY [username],s.problemID,Accepted ORDER BY s.username) AS Seq
    FROM tblSubmission s
    INNER JOIN tblScore sc
    ON sc.problemID = s.problemID
    )t
    GROUP BY [username]


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by six_sic6 Saturday, June 14, 2014 10:16 PM
    Saturday, June 14, 2014 7:01 PM
  • Worked like a charm :) :)

    Thank You

    Saturday, June 14, 2014 10:18 PM