Jump to content

Total value in year.


MoFish
Go to solution Solved by kicken,

Recommended Posts

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
Link to comment
Share on other sites

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

 

 

table.png

 

result.png

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

 

 

totalme.jpg

 

 

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 by MoFish
Link to comment
Share on other sites

  • Solution

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()) 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.