Jump to content


Photo

Total value in year.


Best Answer kicken, 16 January 2014 - 02:38 PM

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

Go to the full post


  • Please log in to reply
14 replies to this topic

#1 MoFish

MoFish

    Advanced Member

  • Members
  • PipPipPip
  • 140 posts

Posted 14 January 2014 - 05:12 PM

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


#2 requinix

requinix

    Transforming Moderator

  • Moderators
  • 6,033 posts
  • LocationWA

Posted 14 January 2014 - 05:17 PM

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

#3 MoFish

MoFish

    Advanced Member

  • Members
  • PipPipPip
  • 140 posts

Posted 14 January 2014 - 05:32 PM

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



#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,223 posts
  • LocationCheshire, UK

Posted 14 January 2014 - 05:41 PM

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, 14 January 2014 - 05:43 PM.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 MoFish

MoFish

    Advanced Member

  • Members
  • PipPipPip
  • 140 posts

Posted 14 January 2014 - 05:43 PM

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


Edited by MoFish, 14 January 2014 - 05:46 PM.


#6 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,627 posts
  • LocationMississauga, Canada

Posted 14 January 2014 - 05:44 PM

Compare outputs of select SQL command!

 

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


Edited by jazzman1, 14 January 2014 - 05:50 PM.


#7 MoFish

MoFish

    Advanced Member

  • Members
  • PipPipPip
  • 140 posts

Posted 14 January 2014 - 05:47 PM

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?



#8 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,223 posts
  • LocationCheshire, UK

Posted 14 January 2014 - 05:52 PM

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.


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#9 MoFish

MoFish

    Advanced Member

  • Members
  • PipPipPip
  • 140 posts

Posted 16 January 2014 - 01:20 PM

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



#10 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,451 posts

Posted 16 January 2014 - 01:34 PM

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.


multi-purpose programming fool. well written source-code should be self-documenting. well written code should be self-troubleshooting. 


#11 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,223 posts
  • LocationCheshire, UK

Posted 16 January 2014 - 01:54 PM

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?


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#12 MoFish

MoFish

    Advanced Member

  • Members
  • PipPipPip
  • 140 posts

Posted 16 January 2014 - 02:00 PM

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, 16 January 2014 - 02:02 PM.


#13 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,451 posts

Posted 16 January 2014 - 02:38 PM

you would need to ORDER BY total


multi-purpose programming fool. well written source-code should be self-documenting. well written code should be self-troubleshooting. 


#14 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,703 posts
  • LocationBonita, FL

Posted 16 January 2014 - 02:38 PM   Best Answer

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


Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#15 MoFish

MoFish

    Advanced Member

  • Members
  • PipPipPip
  • 140 posts

Posted 16 January 2014 - 05:49 PM

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






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com