CODEWITHSUNDEEP
mysql
Muhammad Atif Agha
Muhammad Atif Agha

Reputation: 1545

Adding all the rows of same year using SQL Query instead of php code

I am attaching an image to make my question clear.

enter image description here

I have defined the sections with respect to year, I want that rows of each year should be added to make one row for each year.

For example for Ano 2009 or year 2009, there will be one row, and all of Mes, S, N, O and Total will be the sum of all rows of 2009. Similarly for 2010 and vice versa, so total there will be 3 rows for three years having Ano, Mes, S, N, O and Total.

I can do it programmaticaly using php, but I want to do it using MySQL SQl query. Can anybody help with it?

Upvotes: 0

Views: 92

Answers (3)

Jean-Charles
Jean-Charles

Reputation: 1720

You have to group by the Ano but I think you don't want to sum the month...

If you want to see the total for year/month do :

select   Ano, Mes,  Sum(S),  Sum(N) ,  Sum(O),  Sum(Total)
from 
  MyTable
Group By Ano, Mes

If you want to see the total for year and the numbre of month do : 

select   ... Count(Mes) ...   Group By Ano

If you want to see the total for year do :

select   Ano, Sum(S),  Sum(N) ,  Sum(O),  Sum(Total)
from 
  MyTable
Group By Ano

Upvotes: 0

Hugh Jones
Hugh Jones

Reputation: 2694

select
  Ano,
  Sum(Mes),
  Sum(S),
  Sum(N),
  Sum(O),
  Sum(Total)
from 
  MyTable
Group By Ano

Upvotes: 1

user330315
user330315

Reputation:

A simple group by should do this:

SELECT anno,
       sum(mes) as all_mes,
       sum(s) as all_s,
       sum(n) as all_n,
       sum(o) as all_o
FROM your_table
GROUP BY anno;

To get the total as an additional column, use a derived table:

SELECT t.*, 
       all_mes + all_s + all_n + all_o  as total
FROM ( 
   SELECT anno,
          sum(mes) as all_mes,
          sum(s) as all_s,
          sum(n) as all_n,
          sum(o) as all_o
   FROM your_table
) t
GROUP BY anno;

Upvotes: 0

Related Questions