Closed Thread Icon

Topic awaiting preservation: MySQL sum() help (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=31099" title="Pages that link to Topic awaiting preservation: MySQL sum() help (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: MySQL sum() help <span class="small">(Page 1 of 1)</span>\

 
DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 07-02-2009 20:17

Good afternoon. Having a little trouble with a query I am running.

The premise: I have a database tracking deliveries.
I need to get a summary by week of particular information associated with the deliveries.

The SQL code I am using is as follows:

code:
select 
	week(Schedule.del_date) as week_num,
	sum(SKU.sku_ctn) as ctns_total,
	sum(SKU.sku_vol) as vol_total,
	count(distinct SKU.sku_num) as sku_total,
	count(distinct PO.po_num) as po_total,
	count(distinct Vend.vnd_name) as vnd_total,
	count(distinct Bill.hbl_num) as hbl_total,
	count(distinct Container.cnt_id) as cnt_total
from 
	Schedule
	left join Container on Container.cnt_id = Schedule.cnt_id
	left join Bill on Bill.cnt_id = Container.cnt_id
	left join Vend on Vend.cnt_id = Bill.cnt_id
	left join PO on PO.cnt_id = Vend.cnt_id
	left join SKU on SKU.cnt_id = PO.cnt_id
where
	Container.sts_id = 4
group by 
	week_num
order by 
	week_num asc



This pulls me out a nice array containing, for each week, the week number and a summary of the deliveries for that week.
--> Except that both items that I need to sum() are incorrect.
The count()'s are all accurate. The sum()'s are not - they are off by very large amounts (double, triple, or more). They don't appear to be off in any sort of discernible pattern, or with any particular relationship that I can follow.

I am not sure what to look at as the cause of the error - any thoughts??

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 07-02-2009 21:20

yeah. your count (distinct sku.sku_num...) hides a cartesian product from you.,that your sums show.

start by building a query that gives you a single week, then add in all the aggregation.

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 07-03-2009 15:42

Ok, it took me a while, and some further reading, to understand what you are saying, and why there is a cartesian product...


It seems it would be easiest to do separate a separate query for the sums...?
I have to wait until Monday to play with this further as I can only access it at work.

thanks TP.

« BackwardsOnwards »

Show Forum Drop Down Menu