question

David 11 avatar image
David 11 asked

Is it possible to Join a query with a Count function and sort by the joined fields?

I'm try to count a number of orders sorted by the driver who completed the order. (SQL 2000)

The basic Query works fine.

SELECT d.driver, COUNT(*) filesserved FROM orders d WHERE (deleted = 0) AND (CAST(datepost AS DATETIME) <= '" & strEndDate & "') GROUP BY driver WITH CUBE ORDER BY driver

I would like however to have the results appear alphabetical, in that driver in orders is stored as a driver identification number, so right now, the output comes out by order of the driver id's.

I've been trying to incorporate a join that looks up the last name, first name, and have it order by the last name then first name, instead of by the driver's code, but can't seem to make it work.

I add a JOIN driverdata e ON d.driver = e.driverid after the FROM, and then have tried to add an e.last_name, e.first_name to the query, replacing driver in the group by, and order sections, but consistently get errors. Anyone out there got any suggestions?

sql-server-2000joinsaggregates
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 11 avatar image David 11 commented ·
Thank you, I will give it a try. I am not that familiar with inner joins, I will read up on it too!
0 Likes 0 ·

1 Answer

·
Squirrel 1 avatar image
Squirrel 1 answered
select c.driver, d.last_name, d.first_name, c.filesserved            
from            
(            
    SELECT d.driver, COUNT(*) filesserved             
    FROM   orders d             
    WHERE  (deleted = 0)             
    AND    (CAST(datepost AS DATETIME) <= '" & strEndDate & "')             
    GROUP BY driver WITH CUBE             
) c inner join driverdata d on c.driver = d.driverid            
ORDER BY c.driver            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.