yyhh_2000 avatar image
yyhh_2000 asked

Provide total costload and delivery by DC and by Period

Now load ID should be unique, and only the most recent entry in is valid per load ID. Please provide an Total Cost and Total Deliveries by DC and by Period


10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total. avatar image commented ·
This sounds like a homework/interview question.

As such please provide what you are stuck on, what you have tried so far etc?

0 Likes 0 ·
yyhh_2000 avatar image yyhh_2000 commented ·

Yes, it was a past assessment question. Now I need to go over this again.

My query is like:

select DISTINCT(CostLoad.LOAD_ID), MAX(CostLoad.DATE) as Date, Calendar.PERIOD, CostLoad.DC,

COUNT(CostLoad.COSTLOAD) as CostCount, SUM(CostLoad.COSTLOAD) as CostSum, AVG(CostLoad.COSTLOAD) as CostAverage from CostLoad


INNER JOIN Calendar ON Dates.WM_WEEK = Calendar.WMWEEK

GROUP BY Calendar.PERIOD, CostLoad.DC, CostLoad.DATE, CostLoad.LOAD_ID

ORDER BY Max(CostLoad.DATE) asc, CostLoad.LOAD_ID;

This gives me the wrong number of rows and did not give the sum of total cost by period and DC. My output is like this

my-qurey-result.pngBasically, I did not get the correct rows and did not generate the sums. Please help.

0 Likes 0 ·
my-qurey-result.png (112.0 KiB) avatar image yyhh_2000 commented ·

It looks like your excel list is completely different to your SQL output and the dates don’t match what your trying to do. You got a mix of month/date formatting issues.

What you want is 3 queries not one as you need to group by various different pieces which you can’t do in one.

If you can follow the below link and lost some easily consumable data and a the expected outcome it will be easier to show a working solution.

0 Likes 0 ·

0 Answers


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.