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

Question

question

David 11 asked Apr 9, '10

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-2000 joins aggregates

1

10 |1200

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

David 11 commented · Apr 10, 2010 at 02:47 PM

Thank you, I will give it a try. I am not that familiar with inner joins, I will read up on it too!

0 ·

Answer 1 · 2010-04-10T04:38:45Z

Squirrel 1 answered Apr 10, '10

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

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

question