SQL Query Help

Transact-SQL
1

Hi,

I have a query that I need help with. I am having trouble with getting a nested query to react to the where clause of the whole query.

Example 1:

SELECT Achievement_Level, SUM(Rec_Count), (SELECT SUM(Sum_Count) FROM Temp)
FROM Temp
GROUP BY Achievement_Level

Results 1:

Advanced 5 22
Mastery 7 22
Basic 10 22

What I want is the last column (22) to dynamically change based on my filter.

Example 2:

SELECT Achievement_Level, SUM(Rec_Count), (SELECT SUM(Sum_Count) FROM Temp)
FROM Temp
WHERE Achievement_Level IN ('Advanced','Mastery')
GROUP BY Achievement_Level

Results 2:

Advanced 5 22
Mastery 7 22

I need this to sum to 12, but based on the query, it will still provide a result of 22.

Please let me know if you can help me with this.

Thanks

2

Try:

SELECT Achievement_Level, Rec_Count, 
    SUM(Sum_Count) OVER() AS Total_Count
FROM Temp
WHERE Achievement_Level IN ('Advanced','Mastery')
GROUP BY Achievement_Level
3

Hi Scott,

When I try that, it doesn't sum the Sum_Count column, but instead it gives me a sum count of the distinct achievement_levels. For the example you gave me, that column returns the value of 2.

Also, when I tried to use that, SQL was saying that the SUM_COUNT column must be in the group by.

4

Sorry. No sample data so I couldn't test it first.

5 
   SELECT Achievement_Level, Rec_Count, (SELECT SUM(Sum_Count) FROM Temp)
    FROM Temp
    GROUP BY Achievement_Level

@Skiggity , how can you run the above script ? Because Rec_Count is not in the GROUP BY clause, so, this should be impossible. Please add relevant information.

As @ScottPletcher said, no sample data , we can't help you much.

Something I try, but not having input data and expected output, it's close to a guess.

create table #Temp(
Achievement_Level varchar(50) not null
,Rec_Count int not null
,Sum_Count int not null
)

insert into #Temp(Achievement_Level,Rec_Count,Sum_Count)
values ('Advanced', 5,5)
,('Mastery', 7 ,7)
,('Basic',10,10)

SELECT Achievement_Level
     , SUM(Rec_Count) OVER(Partition by Achievement_Level ) as Rec_Count
     , SUM(Sum_Count) OVER(Order by Achievement_Level 
              Rows between unbounded preceding 
                       and unbounded following) AS Total_Count
FROM #Temp as t
WHERE Achievement_Level IN ('Advanced','Mastery')


 Achievement_Level       Rec_Count           Total_Count
 Advanced                        5                     12
 Mastery                         7                     12

edit, I have something else in my head (voices) :smiley:

SELECT 
   Grp.Achievement_Level
   ,Grp.Rec_Count 
   ,SUM(Rec_Count) OVER() AS Total_Count
FROM
(
SELECT Achievement_Level
     , SUM(Rec_Count) as Rec_Count     
FROM #Temp as t
WHERE Achievement_Level IN ('Advanced','Mastery')
GROUP BY Achievement_Level
)Grp
6 
SELECT t1.Achievement_Level
     , t1.Rec_Count
     , (SELECT SUM(t2.Sum_Count)
          FROM Temp t2
         WHERE t2.Achievement_Level = t1.Achievement_Level)
  FROM Temp t1
 WHERE t1.Achievement_Level IN ('Advanced', 'Mastery')
 GROUP BY
       t1.Achievement_Level
     , t1.Rec_Count;
7

You are correct, sorry about that. I updated my original post to include sum(Rec_Count).