Solved

# How do I sum only 2 columns with a group by that has more columns?

Posted on 2009-12-16
Medium Priority
167 Views
I have a query that inserts these comlumns into another table using a scheduled job but i can't figure out how to get only the rows for Distinct Teacher, TestDate that have > 75 in any hour without including ClassDescription in the grouping.

SELECT DISTINCT [Teacher]
,[TestDate]
,[ClassDescription]
,[Before 7 AM]
,[7 AM]
,[8 AM]
,[9 AM]
,[10 AM]

FROM [#tmp2]

GROUP BY [Teacher]
,[TestDate]
,[ClassDescription]   -- Don't want to group by ClassDescription

HAVING (SUM([Before 7 AM]) > 75)
OR (SUM([7 AM]) > 75)
OR (SUM([8 AM]) > 75)
OR (SUM([9 AM]) > 75)

I've been racking my brain for days trying to figure out a differant route to go with this and keep coming back to 'having'  Please help!!
0
Question by:ehanna1
• 6
• 5

LVL 75

Expert Comment

ID: 26065954
assuming that the testDate stores only the date portion

SELECT DISTINCT [Teacher]
,[TestDate]
,[Before 7 AM]
,[7 AM]
,[8 AM]
,[9 AM]
,[10 AM]

FROM [#tmp2]

GROUP BY [Teacher]
,[TestDate]

HAVING (SUM([Before 7 AM]) > 75)
OR (SUM([7 AM]) > 75)
OR (SUM([8 AM]) > 75)
OR (SUM([9 AM]) > 75)
0

LVL 143

Expert Comment

ID: 26065957
either, you need to remove the column ClassDescription Also from the SELECT list, or use a MAX() function (for example) on it.
0

LVL 143

Expert Comment

ID: 26065961
note: when you have GROUP BY, remove the DISTINCT. does not make any additional sense.
0

Author Comment

ID: 26065977
In t-sql the group by has to contain all items in the select list. I have tried that one....
0

Author Comment

ID: 26066006
This is the select list for an insert statement in which i need the classdescription.
0

LVL 143

Expert Comment

ID: 26066051
but WHICH value of ClassDescription.
as you want to remove the group by, I presume you get different values?
in which case: what is the rule to use to check out which value to use?
``````SELECT [Teacher]
,[TestDate]
,MAX([ClassDescription])
,[Before 7 AM]
,[7 AM]
,[8 AM]
,[9 AM]
,[10 AM]

FROM [#tmp2]

GROUP BY [Teacher]
,[TestDate]

HAVING (SUM([Before 7 AM]) > 75)
OR (SUM([7 AM]) > 75)
OR (SUM([8 AM]) > 75)
OR (SUM([9 AM]) > 75)
``````
0

Author Comment

ID: 26066456
Yes, i you remove the classdescription you get differant values.
as it sits now you get :
662182 2009-12-14 00:00:00.000 VALVE 0 0 0 37 75 12
662182 2009-12-15 00:00:00.000 VALVE 0 0 0 5 79 19
901985 2009-12-14 00:00:00.000 VALVE 0 0 0 53 108 30
But i need it to include the differant classes as well such as this:
Class Inspector Date ....
VALVE 901985 2009-12-14 00:00:00.000 0 0 0 53 108 26
PUMP 901985 2009-12-14 00:00:00.000 0 0 0 0       0    4
VALVE 662182 2009-12-15 00:00:00.000 0 0 0 5     79   19
PUMP 662182 2009-12-15 00:00:00.000 0 0 0 0       0     0
VALVE 662182 2009-12-14 00:00:00.000 0 0 0 37   75   12
It is only giving me the rows that are >75 not the sum of that group >75

0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 26066529
so, you want all the rows, when the GROUP's sum > 75, right?

then, you need to back-join the group by:
``````
SELECT t.*
FROM [#tmp2] t
JOIN ( SELECT [Teacher] ,[TestDate]
FROM [#tmp2]
GROUP BY [Teacher] ,[TestDate]
HAVING (SUM([Before 7 AM]) > 75)
OR (SUM([7 AM]) > 75)
OR (SUM([8 AM]) > 75)
OR (SUM([9 AM]) > 75)
) sq
ON sq.[Teacher] = t.[Teacher]
AND sq.[TestDate] = t.[TestDate]

``````
0

Author Comment

ID: 26066618
Awesome!! that looks like that works!!!!!  And that is called a back-join??

Thank you soo much!!!
0

Author Closing Comment

ID: 31666974
Couldn't have finished without this!!! Thanks!!!
0

LVL 143

Expert Comment

ID: 26066771
>And that is called a back-join??
that's "my term", you "join back" some intermediate results to the table itself...
0

Author Comment

ID: 26066919
I'm still pretty green at SQL. I didn't think that was possible. I didn't find anything online giving any indication to me that you could. I will do more research on joins. Thanks again!!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down â€¦
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a paâ€¦
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month17 days, 6 hours left to enroll

#### 864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.