?
Solved

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

Posted on 2009-12-16
12
Medium Priority
?
167 Views
Last Modified: 2012-05-08
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
Comment
Question by:ehanna1
  • 6
  • 5
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
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

by:Guy Hengel [angelIII / a3]
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

by:Guy Hengel [angelIII / a3]
ID: 26065961
note: when you have GROUP BY, remove the DISTINCT. does not make any additional sense.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ehanna1
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

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

Expert Comment

by:Guy Hengel [angelIII / a3]
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)

Open in new window

0
 

Author Comment

by:ehanna1
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

by:
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]

      

Open in new window

0
 

Author Comment

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

Thank you soo much!!!
0
 

Author Closing Comment

by:ehanna1
ID: 31666974
Couldn't have finished without this!!! Thanks!!!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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

by:ehanna1
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

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.

Join & Ask a Question