Group By and Order By - 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

Analysis Server and Reporting Services (2005)

Group By and Order By

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-04 : 07:46:08

Can any one tell me if using Group By in a query coming into a report will effect the Order By.

When I use this query i can not get the report to Order By the fields I want (each report has to be able to sort by three different fields) and I use this query to get the SUM of the LoadPounds which has repeating values:


SELECT     detail_record.ticket_number AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, cmDelvTo.customer_name AS DeliverToName, 
                      cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, 
                      CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, 
                      SUM(detail_record.pickup_weight) AS PickupPounds, detail_record.hauler_number AS HaulerNumber, SUM(DISTINCT detail_record.ddp_weight) 
                      AS LoadPounds, detail_record.deliver_date
FROM         detail_record INNER JOIN
                      customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
                      customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number
WHERE     (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
                      (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, 
                      detail_record.pickup_dt, detail_record.ticket_number, detail_record.deliver_dt, detail_record.hauler_number, detail_record.deliver_date
ORDER BY TicketNumber

But if I use this query I can sort by any field I want to:


SELECT     detail_record.ticket_number AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, cmDelvTo.customer_name AS DeliverToName, 
                      cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, 
                      CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, 
                      detail_record.pickup_weight AS PickupPounds, detail_record.hauler_number AS HaulerNumber, detail_record.ddp_weight AS LoadPounds, 
                      detail_record.deliver_date
FROM         detail_record INNER JOIN
                      customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
                      customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number
WHERE     (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
                      (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)

I have checked in the properties and set the report to sort by the correct fields and the sort order for each Group using both queries.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:52:03

are you applying grouping in report or in query?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-04 : 11:21:18

In order to do one of the calculations required we are doing the grouping in the query. These posts are all related to problems we are having with creating these reports. None of us have used SSRS before and we are walking through it.

There are 2 calulations we need to do.

1. PickupPounds is the sum of weights in a specfic ticket number. This calculates fine in the report using the second query.

2. LoadPounds is the sum of weights based on the ticket number and a date

Maybe the pictures will help.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 13:03:04

as suggested earlier convert ticketnumber to int using cint() and give it in sorting expression

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources