| Author |
 Topic  |
|
|
Lmele
Starting Member
USA
4 Posts |
 Posted - 08/14/2012 : 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
India
47099 Posts |
Posted - 08/14/2012 : 22:02:05
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Lmele
Starting Member
USA
4 Posts |
Posted - 08/14/2012 : 22:12:11
|
| Brilliant! So simple, but I see now. Many thanks -- you've helped me so much. |
|
|
|
Lmele
Starting Member
USA
4 Posts |
Posted - 08/14/2012 : 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
India
47099 Posts |
Posted - 08/14/2012 : 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
USA
4 Posts |
Posted - 08/15/2012 : 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
India
47099 Posts |
Posted - 08/15/2012 : 10:09:19
|
yep...its like grouping and bringing aggregate data simultaneously with individual details
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Return all rows when using group by
