goose25

Hello to all you SQL experts. Can one of you provide a bit of assistance with the following problem? I've searched my textbook and can't figure out the answer.

I need to create SQL Select statements that would produce running summary files for reports on the following:

Customer volume by month
CustomerID, Month, Total shipments, Total weight

Driver performance by month
Driver ID, Month, Number of manifests on time, Number of manifests delivered late.

Any assistance would be greatly appreciated.

doopstr

select * from Whiskers where porn = "yes"

Anachostic

Do you mean running total? I don't see any columns in your examples that indicate running totals. Your columns look more like a GROUP BY CustomerID/DriverID, Month

Anachostic

Strings are identified with single quotes, so:

select * from Whiskers where porn = 'yes'

rza49311

iTrader: (1)

select Customerid, Month, sum(total shipments) as [Total Shipments], sum(total weight) as [Total Weight]
from table
group by Customerid, Month
order by Customerid, Month

Like so?

Anachostic

Yes, that's what I'm thinking. Unless that's not what you want, then no.

leftride

iTrader: (1)

that or:

SELECT a.customerid, a.month, SUM(b.[total shipments]), SUM(b.[total weight])
FROM table a
JOIN table b
ON a.customerid = b.customerid
AND b.month <= a.month
GROUP BY a.customerid, a.month
ORDER BY 1,2

and the same general idea for the second one. Not sure the definitions of 'volume' or 'performance'

goose25

Thanks a bunch man, that's exactly what i was looking for.

Anachostic

I just (re)read this solution. Joining the table to itself is a clever idea I hadn't thought of before. I was going to go with a subselect statement. I think I'm going to use that trick tomorrow. Thanks!

Whiskers

I divided by 0 and my penis exploded...

leftride

iTrader: (1)

glad that helped goose.

Love the SQL problems

rza49311

iTrader: (1)

yeah, we use that trick often, heck we have some queries where the same table is aliased 3 times