Total value in year.

[MoFish]

Hi,

 

I have the following query which works and calculates an amount as 'total' - however the total value appears to be including values from the year 2013.

 

Does the sum part exclude the where clause?

 

I was hoping it would only give me the total for the current year.

 

How would this therfore be restructured to only give me the 'amount' for the current year?

 

Thanks,

 

MoFish

SELECT SUM(amount) AS total,
sales_staff.id, sales_staff.name, sales_staff.icon, sales_league.`id`, sales_league.`amount`, sales_league.`money_received`, sales_league.`closedby_id`, sales_league.`new_customer`, sales_league.`date_added`, sales_league.`time_added`
FROM sales_staff
JOIN sales_league ON
sales_league.staff_id = sales_staff.id
WHERE YEAR(date_added) = YEAR(CURDATE())
ORDER BY amount DESC LIMIT 1

[requinix]

It will only do the current year. Why do you say it has values from 2013?

[MoFish]

Hi Requnix,

 

It appers to be adding all the values for 'amount' where staff_id = 1 in this example - which works out to be 17000.

 

But the 3rd one has a date of 2013 so thought this would have been excluded from the calculation?

 

Thanks,

 

MoFish

 

 

 

[Barand]

It's adding all 2014 amounts - total = 17,000

 

You have an aggregation query without a GROUP BY so you only get 1 row returned, containing data from the first row. All the columns selected other then the SUM are superfluous if you only want the total for the year

Edited January 14, 2014 by Barand

[MoFish]

Ahh... Thanks I should be able to amend it accordingly. Ideally I wanted it to base it on the staff_id

Edited January 14, 2014 by MoFish

[jazzman1]

Compare outputs of select SQL command!

 

SELECT YEAR(2014-01-12) TO SELECT YEAR(CURDATE()).

Edited January 14, 2014 by jazzman1

[MoFish]

SELECT SUM( amount ) AS total, sales_staff.id, sales_staff.name, sales_staff.icon, sales_league.`id` , sales_league.`amount` , sales_league.`money_received` , sales_league.`closedby_id` , sales_league.`new_customer` , sales_league.`date_added` , sales_league.`time_added`
FROM sales_staff
JOIN sales_league ON sales_league.staff_id = sales_staff.id
WHERE YEAR( date_added ) = YEAR( CURDATE( ) )
GROUP BY staff_id
ORDER BY amount DESC
LIMIT 1 

GROUP BY should do the trick?

[Barand]

Bear in mind that data from the league table that is not aggregated will be meaningless with the group by. It normally takes the values from the first record in each group but the manual states unspecified values.

[MoFish]

You appear correct, the data is still not correct using the group by

[mac_gyver]

without knowing what result you are getting and what result you expect, it's not really possible to help base on - "the data is still not correct"

 

best guess is there should be no LIMIT clause as that would give just the row for the highest amount.

[Barand]

When you use GROUP BY staff_id you get a single row for each staff. Aggregates (eg SUM) and data which relates to a single staff id (such as name, and icon) will also be OK. The problem is where you have many records related to that staff if as in the sales_league table - you can only pull data from one of them unless you aggregate (eg SUM(money_received), MAX(date_added) etc.

 

As Mac_gyver asked, what are you trying to achieve?

[MoFish]

Hi,

 

I am trying calculate all the values in the 'amount' column for each individual staff member (staff_id) and return this as 'total' for the person with the greatest value.

 

This will be based on current year, current month, current week.

 

I understand I dont need to select all these values, but the calculation doesnt appear correct.

 

My Current Query For Year:

SELECT SUM(amount) AS total, 
sales_staff.`id`, sales_staff.`name`, sales_staff.`icon`,
sales_league.`id` , sales_league.`amount` , sales_league.`money_received` , sales_league.`closedby_id` , sales_league.`new_customer` , 
sales_league.`date_added` , sales_league.`time_added` 
FROM sales_staff 
JOIN sales_league 
ON sales_staff.id = sales_league.staff_id 
WHERE YEAR(date_added) = YEAR(CURDATE()) 
GROUP by staff_id 
ORDER BY amount DESC LIMIT 1

Returns:

Year: £3596

 

My Current Query For Month:

SELECT SUM(amount) AS total, 
sales_staff.`id`, sales_staff.`name`, sales_staff.`icon`, 
sales_league.`id` , sales_league.`amount` , sales_league.`money_received` , sales_league.`closedby_id` , sales_league.`new_customer` , 
sales_league.`date_added` , sales_league.`time_added` 
FROM sales_staff 
JOIN sales_league 
ON sales_staff.id = sales_league.staff_id 
WHERE YEAR(date_added) = YEAR(CURDATE()) AND MONTH(date_added) = MONTH(CURDATE()) 
GROUP by staff_id 
ORDER BY amount DESC LIMIT 1 

Returns
Month:  £3596

 

My Current Query For Week

SELECT SUM(amount) AS total, 
sales_staff.`id`, sales_staff.`name`, sales_staff.`icon`, 
sales_league.`id` , sales_league.`amount` , sales_league.`money_received` , sales_league.`closedby_id` , sales_league.`new_customer` , 
sales_league.`date_added` , sales_league.`time_added` 
FROM sales_staff 
JOIN sales_league 
ON sales_staff.id = sales_league.staff_id 
WHERE YEAR(date_added) = YEAR(CURDATE()) AND WEEK(date_added) = WEEK(CURDATE()) 
GROUP by staff_id 
ORDER BY amount DESC LIMIT 1 

Returns:

Week: £2100

 

From the table below I would of expected staff_id = 2 total for the month to be around £6000 based on the amounts in the red column below?

 

 

 

 

In summary,

 

I'm looking to look at the table and find out who has sold the most based on the 'amount' field and return this as a totals value along with there name, id etc for the current (year, month, week)

 

Thanks,

 

MoFish

Edited January 16, 2014 by MoFish

[mac_gyver]

you would need to ORDER BY total

[kicken]

From the table below I would of expected staff_id = 2 total for the month to be around £6000 based on the amounts in the red column below?

Why? If I add up all the rows where staff_id=2 and the date is 01/2014 I get a total of  7780.

 

Since your columns other than staff_id and amount are irrelevant, don't select them to keep your query simple.

SELECT 
  SUM(amount) AS total, 
  sales_staff.`id`
FROM sales_staff 
JOIN sales_league ON sales_staff.id = sales_league.staff_id 
WHERE 
  YEAR(date_added) = YEAR(CURDATE()) 
  AND MONTH(date_added) = MONTH(CURDATE()) 
GROUP by staff_id 

If you want to check what rows mysql is adding up, remove the SUM() and GROUP BY and run the query again:

SELECT 
  amount AS total, 
  sales_staff.`id`
FROM sales_staff 
JOIN sales_league ON sales_staff.id = sales_league.staff_id 
WHERE 
  YEAR(date_added) = YEAR(CURDATE()) 
  AND MONTH(date_added) = MONTH(CURDATE()) 

[MoFish]

Thank you for your help folks - its working as expected now.