Aggragate function - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Aggragate function

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-03 : 03:03:49

I need to SUM all the weights in each group of Divisions for an SSRS report. I am currently learning this on the fly with little help available. The SQL I am using gives me Grouping of the divisions, but now I need to sum each Group.

Example:

GROUP1 100
GROUP2 100
GROUP3 100
GROUP2 100
GROUP1 100
GROUP1 100
GROUP3 100
GROUP1 100
GROUP2 100
GROUP1 100

Results in:
GROUP1 500
GROUP2 300
GROUP3 200

SELECT detail_record.division, SUM(detail_record.pickup_weight) AS TotalPickupWeight, detail_record.pickup_dt, detail_record.customer_bill_to,
customer_master.customer_number, customer_master.customer_name, customer_master.city, detail_record.pickup_date, detail_record.ticket_number,
detail_record.customer_delv_to
FROM detail_record INNER JOIN
customer_master ON detail_record.customer_bill_to = customer_master.customer_number AND
detail_record.customer_bill_to = customer_master.customer_number
WHERE (detail_record.customer_bill_to = @customer_bill_to) AND (detail_record.pickup_date BETWEEN @StartDate AND @EndDate)
GROUP BY detail_record.division, detail_record.pickup_dt, detail_record.customer_bill_to, customer_master.customer_number,
customer_master.customer_name, customer_master.city, detail_record.pickup_date, detail_record.ticket_number,
detail_record.customer_delv_to

What a way to start the new year.

It has been suggested that I do a two part query.... take the large one I have now... but remove the aggregates.... then take the smaller one with the aggregates, and do a JOIN to it on the Division to get the data together. Can someone show how this can be done as I have never done a JOIN for two queries?

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 06:51:29

You can do this aggregation in report. just add a group to your report table on divisions field. then give expression as
=SUM(Fields!TotalPickupWeight.value)

this will give one row per division with total weight value for the division.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-03 : 07:25:25

select detail,sum(value) as total from test4 group by detail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 07:33:50

quote:
Originally posted by ashishashish

select detail,sum(value) as total from test4 group by detail

i think ops problem is he needs to add extra fields also to result set. thats why i suggested to do it in report.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 07:35:50

alternativey you could do this


SELECT detail_record.division, 
SUM(detail_record.pickup_weight) OVER (PARTITION BY detail_record.division) AS TotalPickupWeight,
detail_record.pickup_dt, 
detail_record.customer_bill_to, 
customer_master.customer_number, 
customer_master.customer_name, 
customer_master.city, 
detail_record.pickup_date, 
detail_record.ticket_number, 
detail_record.customer_delv_to
FROM detail_record INNER JOIN
customer_master ON detail_record.customer_bill_to = customer_master.customer_number AND 
detail_record.customer_bill_to = customer_master.customer_number
WHERE (detail_record.customer_bill_to = @customer_bill_to) AND (detail_record.pickup_date BETWEEN @StartDate AND @EndDate)

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-03 : 07:53:14

Thanks Sirrrrrr.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 07:57:00

Welcome

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-03 : 09:28:27

Thanks for the advice, I will give it a try both ways and post back as to how it works out. It was interesting to see how to join the SQL onto itself.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 09:45:42

No problem
Let us know how you got on

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-04 : 10:11:27

If I do the SUM of in the report as a group of division, should I remove it from the SQL statement?

Also can I do BETWEEN StartDate and EndDate without using a variable so I don't have to do "WHERE VarDate BETWEEN StartDate AND EndDate"?

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-04 : 12:42:21

quote:
Originally posted by CoachBarker

If I do the SUM of in the report as a group of division, should I remove it from the SQL statement?

Also can I do BETWEEN StartDate and EndDate without using a variable so I don't have to do "WHERE VarDate BETWEEN StartDate AND EndDate"?

Thanks for the help
CoachBarker

yup. you need to remove sum and bring only detail info i.e field detail_record.pickup_weight in query if you're planning to do sum in report.
Yup. you can do BETWEEN start and end date without variable. but you have to hardcode values in that case. the use variables is recommended so as increase reusability so that you can resuse procedure for different set of dates by passing different values for parameter variables.

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-04 : 14:19:34

What I had done in the report is hardcode varDte to be equal to the StartDate, in this case I am using the date/time picker in the reports parameters to select the dates.

When using the Report Service is it best to just run an SQL that reurns all the fields needed and then do any changes in the report?

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:32:39

quote:
Originally posted by CoachBarker

What I had done in the report is hardcode varDte to be equal to the StartDate, in this case I am using the date/time picker in the reports parameters to select the dates.

When using the Report Service is it best to just run an SQL that reurns all the fields needed and then do any changes in the report?

Thanks for the help
CoachBarker

in you case both are fine, doing aggregation in query or in report. but bring aggegate in query will prevent you from using detail data just in case you need it for any other part of report.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources