Search WHERE SUM

2003-07-28 Thread Yoed Anis 
Hello,

What would be the proper way of generating a query like the following;

SELECT id FROM sales WHERE SUM( totalsales) = '2' GROUP BY id;

Assuming the table looks like this;
Sales:

Id| totalsales| monthyear
1 | 100 | 2003-09
1 | 1   | 2003-08
1 | 2000| 2003-07
2 | 3   | 2003-05
3 | 1   | 2003-06
3 | 1   | 2003-05

I want the query to return id 1 and 2 as the sum of their totalsales is
smaller then 2.


Thanks for your help,
Best,
Yoed


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Search WHERE SUM

2003-07-28 Thread Kevin Fries 
Easy:

SELECT id FROM sales 
GROUP BY id
HAVING SUM( totalsales) = 2 ;

That will perform the grouping, by ID, then filter and display only
calculated rows with a sum less than 2.

 -Original Message-
 From: Yoed Anis [mailto:[EMAIL PROTECTED] 
 Sent: Monday, July 28, 2003 10:22 AM
 To: [EMAIL PROTECTED]
 Subject: Search WHERE SUM
 
 
 Hello,
 
 What would be the proper way of generating a query like the following;
 
 SELECT id FROM sales WHERE SUM( totalsales) = '2' GROUP BY id;
 
 Assuming the table looks like this;
 Sales:
 
 Id| totalsales| monthyear
 1 | 100   | 2003-09
 1 | 1 | 2003-08
 1 | 2000  | 2003-07
 2 | 3 | 2003-05
 3 | 1 | 2003-06
 3 | 1 | 2003-05
 
 I want the query to return id 1 and 2 as the sum of their 
 totalsales is smaller then 2.
 
 
 Thanks for your help,
 Best,
 Yoed
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql? [EMAIL PROTECTED]
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Search WHERE SUM

2003-07-28 Thread Jay Blanchard 
[snip]
SELECT id FROM sales WHERE SUM( totalsales) = '2' GROUP BY id;
[/snip]

SELECT id, SUM(totalsales) AS TotalSales
FROM sales
GROUP BY id
HAVING TotalSales = '2'

HTH!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]