# Total value in year.

Best Answer kicken, 16 January 2014 - 07: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
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

```
Go to the full post

14 replies to this topic

### #1 MoFish

MoFish
• Members
• 210 posts

Posted 14 January 2014 - 10: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,
FROM sales_staff
JOIN sales_league ON
sales_league.staff_id = sales_staff.id
ORDER BY amount DESC LIMIT 1
```

### #2 requinix

requinix
• 9,370 posts
• LocationWA

Posted 14 January 2014 - 10:17 PM

It will only do the current year. Why do you say it has values from 2013?
The Reimann Zeta Function Trolley Problem | "Summer is when I, the great ice fairy, can show my true power!"

### #3 MoFish

MoFish
• Members
• 210 posts

Posted 14 January 2014 - 10: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

### #4 Barand

Barand
• Moderators
• Sen . ( ile || sei )
• 17,838 posts

Posted 14 January 2014 - 10: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 - 10:43 PM.

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

 |baaGrid| easy data tables - and more|baaChart| easy line, column and pie charts

### #5 MoFish

MoFish
• Members
• 210 posts

Posted 14 January 2014 - 10: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 - 10:46 PM.

### #6 jazzman1

jazzman1
• Moderators
• 2,715 posts

Posted 14 January 2014 - 10:44 PM

Compare outputs of select SQL command!

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

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

### #7 MoFish

MoFish
• Members
• 210 posts

Posted 14 January 2014 - 10: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
• Moderators
• Sen . ( ile || sei )
• 17,838 posts

Posted 14 January 2014 - 10: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.

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

 |baaGrid| easy data tables - and more|baaChart| easy line, column and pie charts

### #9 MoFish

MoFish
• Members
• 210 posts

Posted 16 January 2014 - 06:20 PM

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

### #10 mac_gyver

mac_gyver
• Staff Alumni
• Staff Alumni
• 4,071 posts

Posted 16 January 2014 - 06: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
• Moderators
• Sen . ( ile || sei )
• 17,838 posts

Posted 16 January 2014 - 06: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?

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

 |baaGrid| easy data tables - and more|baaChart| easy line, column and pie charts

### #12 MoFish

MoFish
• Members
• 210 posts

Posted 16 January 2014 - 07: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` ,
FROM sales_staff
JOIN sales_league
ON sales_staff.id = sales_league.staff_id
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` ,
FROM sales_staff
JOIN sales_league
ON sales_staff.id = sales_league.staff_id
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` ,
FROM sales_staff
JOIN sales_league
ON sales_staff.id = sales_league.staff_id
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 by MoFish, 16 January 2014 - 07:02 PM.

### #13 mac_gyver

mac_gyver
• Staff Alumni
• Staff Alumni
• 4,071 posts

Posted 16 January 2014 - 07: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
• Gurus
• Wiser? Not exactly.
• 3,313 posts
• LocationBonita, FL

Posted 16 January 2014 - 07: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
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

```

Did I help you out? Feeling generous? I accept tips via Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7
Kicken's World⦄ ⦃Recycle old CD's

### #15 MoFish

MoFish
• Members
• 210 posts

Posted 16 January 2014 - 10: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