CODEWITHSUNDEEP
sqlselect
newbieHQ
newbieHQ

Reputation: 69

compute and sum the data from a table based on month

i would like to compute the data from a table where it consists date, and data ..there are a lot of data inside the table. example are as below:

DATE       hour  data1  data2
-------------------------------
01/01/2010  1    10860  1234    
01/01/2010  2    10861  1234    
01/01/2010  3    10862  1234    
01/01/2010  4    10863  567   
01/01/2010  5    10864  458    
02/01/2010  1    10865  3467    
02/01/2010  2    10866  7890    
02/01/2010  3    10867  863    
02/01/2010  4    10868  0    
02/01/2010  5    10868  698  
03/01/2010  1    10868  4693  
03/01/2010  2    10868  7853  
03/01/2010  3    10868  5987

and from above data. i would like to compute it into a table like this: 

DATE           sdata1  sdata2
-------------------------------
01/01/2010     54310   4727    
02/01/2010     54334   12918    
03/01/2010     32604   18533

the sdata1 and sdata2 are the sum of the data that computed ... is there any way for me to compute the data into above data display? thank you ... hehe ...

Upvotes: 0

Views: 166

Answers (1)

Mike S.
Mike S.

Reputation: 4869

SELECT DATE, SUM(data1) AS sdata1, SUM(data2) AS sdata2 FROM table1 GROUP BY DATE;

Try this and let me know if that is what you're looking for. You can also use the WHERE DATE BETWEEN startDate AND endDate if you needed a range.

Another trick if you wanted this data in another table to do "stuff" with, would be to: 

CREATE TABLE table2 (SELECT DATE, SUM(data1) AS sdata1, SUM(data2) AS sdata2 FROM table1 GROUP BY DATE);

This will perform query above but create a new table with the results. I'm not sure why you might need that but sometimes de-normalizing and having computed information in a table for display can speed up your app.

If you have a table already then you can do the following:

INSERT INTO table2 (DATE, sdata1, sdata2) VALUES (SELECT DATE, SUM(data1) AS sdata1, SUM(data2) AS sdata2 FROM table1 GROUP BY DATE);

Upvotes: 3

Related Questions