CODEWITHSUNDEEP
mysqlsql
mk_89
mk_89

Reputation: 2742

Grouping by Week giving inaccurate results

I am trying to get all my sales from a table and group them by each week but I am getting the wrong week range.

table structure

order_id,  sell_price, buy_price, time

Sales per Day

Sell Price   Fees     Buy_Price    Margin   Date
181.98       36.39    78.43        67.154   03-12-2012
65.49        13.08    30.34        22.052   02-12-2012
44.21        8.84     19.45        15.918   29-11-2012
84.46        16.89    32.39        35.178   27-11-2012
106.52       21.30    61.66        23.556   26-11-2012
113.92       22.78    44.74        46.396   25-11-2012
64.74        12.94    26.32        25.472   24-11-2012
16.11        3.22     4.20         8.688    23-11-2012
13.81        2.76     3.62         7.428    22-11-2012

Sales per Week

Sell Price   Fees     Buy_Price    Margin   Date
247.47       49.494   108.77       89.206   03-12-2012
349.11       69.822   158.24       121.048  26-11-2012
94.66        18.932   34.14        41.588   24-11-2012

The problem with last table as you may have noticed is that the figures don't add up, the first row should be calculating results from the 3-11-2012 to present (which means the margin should be 67), while the 2nd should be calculating results from the 26-11-12 to 2-11-12 (which means the margin should be around 95).

The query I am using is below, what could possibly the problem with it as I can not see one.

SQL

SELECT 
ROUND(SUM(sell_price),2) AS sell_price, 
ROUND(SUM(buy_price/100*20),2) AS fees, 
ROUND(SUM(buy_price),2) AS buy_price, 
ROUND(SUM(sell_price/100*80 - buy_price),2) AS margin, 
time 
FROM order_items 
GROUP BY WEEK(FROM_UNIXTIME(time)) 
ORDER BY WEEK(FROM_UNIXTIME(time)) DESC

Upvotes: 1

Views: 60

Answers (1)

cha
cha

Reputation: 10411

Too easy. The week in your instance of mysql starts from Sunday. You want it calculated from Monday. You need to pass a second parameter to the WEEK function. In your case it will be 1 (I think). However, I recommend you to adjust the default_week_format system variable to match your country's Regional Settings.

Upvotes: 1

Related Questions