Multiple Aggregate Functions - 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)

Multiple Aggregate Functions

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-05 : 10:51:52

OK so I am using this query to return data for my report and it works fine now that I am doing the AGGREGATE FUNCTION in the report:

SELECT detail_record.division, detail_record.pickup_weight, detail_record.pickup_dt, detail_record.customer_bill_to, detail_record.customer_delv_to,
detail_record.pickup_date, customer_master.customer_number, customer_master.customer_name, customer_master.city, detail_record.pickup_date,
detail_record.ticket_number
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)
ORDER BY detail_record.pickup_date

Now I need to add 2 additional SUM(FIELDS) that need to come in with the query, I have tried variations of this in SQL Server Management Studio but none of them have worked:

SELECT detail_record.division, detail_record.pickup_weight, detail_record.pickup_dt,detail_record.customer_delv_to,
detail_record.customer_bill_to, customer_master.customer_number, customer_master.customer_name, customer_master.city,
detail_record.pickup_date, detail_record.ticket_number,
SUM(detail_record.pickup_weight) AS BILL_TO WHERE detail_record.customer_bill_to = @customer_number,
SUM(detail_record.pickup_weight) AS DELV_TO WHERE detail_record.customer_delv_to = @customer_number

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_number) AND (detail_record.pickup_date BETWEEN @StartDate AND @EndDate)
ORDER BY detail_record.pickup_weight

Any help would be greatly appreciated

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:54:05

[code]
SELECT detail_record.division, detail_record.pickup_weight, detail_record.pickup_dt,detail_record.customer_delv_to,
detail_record.customer_bill_to, customer_master.customer_number, customer_master.customer_name, customer_master.city,
detail_record.pickup_date, detail_record.ticket_number,
SUM(CASE WHEN detail_record.customer_bill_to = @customer_number THEN detail_record.pickup_weight ELSE 0 END) AS BILL_TO ,
SUM(CASE WHEN detail_record.customer_delv_to = @customer_number THEN detail_record.pickup_weight ELSE 0 END) AS 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_number) AND (detail_record.pickup_date BETWEEN @StartDate AND @EndDate)
ORDER BY detail_record.pickup_weight
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-05 : 10:55:51

quote:
Originally posted by CoachBarker

OK so I am using this query to return data for my report and it works fine now that I am doing the AGGREGATE FUNCTION in the report:

SELECT detail_record.division, detail_record.pickup_weight, detail_record.pickup_dt, detail_record.customer_bill_to, detail_record.customer_delv_to,
detail_record.pickup_date, customer_master.customer_number, customer_master.customer_name, customer_master.city, detail_record.pickup_date,
detail_record.ticket_number
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)
ORDER BY detail_record.pickup_date

Now I need to add 2 additional SUM(FIELDS) that need to come in with the query, I have tried variations of this in SQL Server Management Studio but none of them have worked:

SELECT detail_record.division, detail_record.pickup_weight, detail_record.pickup_dt,detail_record.customer_delv_to,
detail_record.customer_bill_to, customer_master.customer_number, customer_master.customer_name, customer_master.city,
detail_record.pickup_date, detail_record.ticket_number,
SUM(detail_record.pickup_weight) AS BILL_TO WHERE detail_record.customer_bill_to = @customer_number,
SUM(detail_record.pickup_weight) AS DELV_TO WHERE detail_record.customer_delv_to = @customer_number

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_number) AND (detail_record.pickup_date BETWEEN @StartDate AND @EndDate)
Group by detail_record.division, detail_record.pickup_weight, detail_record.pickup_dt,detail_record.customer_delv_to,
detail_record.customer_bill_to, customer_master.customer_number, customer_master.customer_name, customer_master.city,
detail_record.pickup_date, detail_record.ticket_number
ORDER BY detail_record.pickup_weight

Any help would be greatly appreciated

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 11:00:25

as before you can do this in report also. just use your original query itself and in report use below expressions,
=Sum(IIF(Fields!customer_bill_to.value=Parameters!customer_number.value,Fields!pickup_weight.value,0)) for BILL_TO
and

=Sum(IIF(Fields!customer_delv_to.value=Parameters!customer_number.value,Fields!pickup_weight.value,0)) for DELV_TO

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-05 : 11:42:52

Setting the values in the report I get this error message

[rsCompilerErrorInExpression] The Value expression for the textbox ‘textbox25’ contains an error: [BC30205] End of statement expected.
[rsCompilerErrorInExpression] The Value expression for the textbox ‘textbox27’ contains an error: [BC30205] End of statement expected.
Build complete -- 2 errors, 0 warnings

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 11:47:53

sure that you used this?
=Sum(IIF(Fields!customer_bill_to.value=Parameters!customer_number.value,Fields!pickup_weight.value,0))

=Sum(IIF(Fields!customer_delv_to.value=Parameters!customer_number.value,Fields!pickup_weight.value,0))

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-05 : 12:15:29

using the last bit of code:

=Sum(IIF(Fields!customer_bill_to.value=Parameters!customer_number.value,Fields!pickup_weight.value,0))

=Sum(IIF(Fields!customer_delv_to.value=Parameters!customer_number.value,Fields!pickup_weight.value,0))

it is returning the last value that it should be summing, example:
Division 1 100
Division 2 75
Division 3 125
Division 4 200

It is only returning Division 200.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:20:42

quote:
Originally posted by CoachBarker

using the last bit of code:

=Sum(IIF(Fields!customer_bill_to.value=Parameters!customer_number.value,Fields!pickup_weight.value,0))

=Sum(IIF(Fields!customer_delv_to.value=Parameters!customer_number.value,Fields!pickup_weight.value,0))

it is returning the last value that it should be summing, example:
Division 1 100
Division 2 75
Division 3 125
Division 4 200

It is only returning Division 200.

Thanks for the help
CoachBarker

whats the value of Parameters!customer_number.value? does all the others satisfy this condition Fields!customer_bill_to.value=Parameters!customer_number.value?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-05 : 13:39:15

Trying to figure out to debug and set a breakpoint in VS for reports, never did one before.

Thanks for the help
CoachBarker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources