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.

 

select * from Whiskers where porn = "yes"

 

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

 

Strings are identified with single quotes, so:

select * from Whiskers where porn = 'yes'

 

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?

 

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

 

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'

 

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

 

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!

 

I divided by 0 and my penis exploded...

 

glad that helped goose.

Love the SQL problems

 

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