Return all rows when using group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return all rows when using group by

Author

Topic

Lmele
Starting Member

4 Posts

Posted - 2012-08-14 : 21:48:17

I'm a SQL novice, and am trying to return all rows from a query which meet the conditions of a group by clause. Specifically (I will try hard to make this clear), I want to return all rows which have the sum of a column which equals 6 or greater for all rows of a certain date. In other words, my select will have the columns: date, units, etc. There might be four rows with the date 1/1/2012, two rows with a unit value of 1 and two rows with a unit value of 2. So the sum of the unit values for all of the rows for 1/1/2012 is 6, and therefore I want to return each of these rows. Then there might be four rows from 1/2/2012, three rows with a unit value of 1 and one row with a unit value of 2. The sum of the units for these four rows is 5, and so I don't want to return them.

I have the query below which returns (using a group by) one row for each date which meets my unit-sum date criteria. But in my final output, I need all rows for each of these qualified dates, and I can't figure out how to select that while simultaneously using the group by. Any advice would be sincerely appreciated. Thanks!

select people_id,childs_name,event_name, date_of_service,sum(duration) as day_sum_duration
from evolv_cs.dbo.pk_rpt_isc_osc_view
where actual_date between '2012-6-1' and '2012-7-1'
group by people_id,childs_name,event_name,date_of_service
having sum(duration) >=5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 22:02:05

[code]
select * from
(
select *,sum(duration) over (partition by date_of_service) as day_sum_duration
from evolv_cs.dbo.pk_rpt_isc_osc_view
where actual_date between '2012-6-1' and '2012-7-1'
)t
where day_sum_duration >5
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lmele
Starting Member

4 Posts

Posted - 2012-08-14 : 22:12:11

Brilliant! So simple, but I see now. Many thanks -- you've helped me so much.

Lmele
Starting Member

4 Posts

Posted - 2012-08-14 : 23:12:43

Sorry, I spoke too soon. Your query is not quite right because it sums the day duration for all rows with the same date, and I need it to sum the day duration only for rows with the same people_id (hence the group by in my original query). I've been running variations of your query, trying to include the group by, but I can't get it. Thanks for any further advice....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 23:41:11

here you go


select * from
(
select *,sum(duration) over (partition by people_id,date_of_service) as day_sum_duration 
from evolv_cs.dbo.pk_rpt_isc_osc_view
where actual_date between '2012-6-1' and '2012-7-1'
)t
where day_sum_duration >5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lmele
Starting Member

4 Posts

Posted - 2012-08-15 : 08:15:00

Now it works perfectly. So the partition is like a group by. I really appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 10:09:19

yep...its like grouping and bringing aggregate data simultaneously with individual details

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources