SQL using Group and Order

[CoachBarker]

Maybe we did not cover this in school, at least not that I can find. I am writing reports using SSRS and I have an SQL Statement. Can I use Order by and Group by in the same statement?

SELECT detail_record.detail_number, detail_record.ticket_number, detail_record.deliver_dt, detail_record.pickup_dt, detail_record.hauler_number,
detail_record.grand_total_wt, detail_record.division, detail_record.pickup_weight, used_ticket_no.pu_date, detail_record.customer_bill_to
FROM detail_record INNER JOIN
used_ticket_no ON detail_record.ticket_number = used_ticket_no.ticket_number
WHERE (detail_record.ticket_number = @ticket_number) AND (used_ticket_no.pu_date = @pickup_dt) AND (detail_record.customer_bill_to = @customer_bill_to)
GROUP BY detail_record.pickup_dt
ORDER BY detail_record.ticket_number

Error Message:


I am learning SSRS at work and I am doing it all on my own so there is no one to ask.

[techgnome]

yesm you can use the two together.... but that's not your problem... your problem is that you are using the Group By clause, but provided NO AGGREGATE FUNCTIONS (max, min, sum, avg, etc).... on the rest of the fields... that's what the error message is about... has NOTHING to do with the order by.

-tg

[CoachBarker]

Ah now that rings a bell In the SSRS report I need to sum the amounts in detail_record.grand_total_wt and detail_record.pickup_weight by Group. I could do that in the query instead of in the report and get the same Reults.

In an old assignment I did find a couple of examples, not using both, but using them separately, So I came up with this.

SELECT detail_record.detail_number, detail_record.ticket_number, detail_record.deliver_dt, detail_record.pickup_dt, detail_record.hauler_number,
detail_record.grand_total_wt, detail_record.division, detail_record.pickup_weight, used_ticket_no.pu_date, detail_record.customer_bill_to

SUM(detail_record.grand_total_wt) AS grandTotalWeight, SUM(detail_record.pickup_weight) AS totalPickupWeight

FROM detail_record INNER JOIN
used_ticket_no ON detail_record.ticket_number = used_ticket_no.ticket_number
WHERE (detail_record.ticket_number = @ticket_number) AND (used_ticket_no.pu_date = @pickup_dt) AND (detail_record.customer_bill_to = @customer_bill_to)


GROUP BY detail_record.pickup_dt
ORDER BY detail_record.ticket_number

Any suggestions on how to write this so it should work?

[techgnome]

BUUUUT.... if there are ANY fields that ARE NOT part of the aggregate. they MUST BE in the group by....

Simplifying it some:
SELECT A, B, C, X, Y, X FROM someTable
will (obviously) display everything in what ever order.

Applying an order to A and X...
SELECT A, B, C, X, Y, X FROM someTable ORDER BY A, X
Now we're sorting.

OK, on to the GROUP BY....
A simple group by is like this:
SELECT A, SUM(B) FROM someTable GROUP BY A

So now, for each A, we get the sums of all Bs.

Now, let's muck it up... include C...
SELECT A, SUM(B) AS B_Tot, SUM(C) AS C_Tot FROM someTable GROUP BY A

Now, for each A, we get the sums of B and C....

But if we only want the Sum of B... it has to be like this:
SELECT A, SUM(B) AS B_Tot, C FROM someTable GROUP BY A, C
Now, what we get is for EACH A/C combination, the Sum of B....
We do that because C isn't part of the aggregate any more, SQL doesn't know what to do with it... so you have to add it to the group by clause...

SELECT A, SUM(B) AS B_Tot, C, D, MIN(X) AS X_Min, MAX(Y) AS Y_Max, AVG(Z) AS Z_Avg FROM someTable GROUP BY A, C, D

Here, because B, X, Y, & Z are part of an aggregate function, they are NOT part of the group by... but A, C, & D are....

Meanwhile we can still apply a sort to it:
SELECT A, SUM(B) AS B_Tot, C, D, MIN(X) AS X_Min, MAX(Y) AS Y_Max, AVG(Z) AS Z_Avg FROM someTable GROUP BY A, C, D ORDER BY D, A, C

IS this helping any?

-tg

[CoachBarker]

To early for all that Yeah I think I got it, I will work on it and post back later.