CODEWITHSUNDEEP
phpmysqlsqlselectsum
Rohitashv Singhal
Rohitashv Singhal

Reputation: 4557

Get Sum using php and SQL is date is same

I have a db structure as follows:

Date         Morning    Evening Qty(Ltr)  Total 
13-May-2015   101         10                111 
15-May-2015   10          1.25              11.25   
15-May-2015   10          2.25              12.25   
15-May-2015   101         10                111 
16-May-2015   10          1.25             11.25    
16-May-2015   10          2.25             12.25    
17-May-2015   10          2.25             12.25

What I want is to create a new table in which the data with the same date should be in one row only, means duplicate dates should be added. How can I do this ?

I want the result

Date         Morning    Evening Qty(Ltr)  Total 
13-May-2015   101         10                111 
15-May-2015   121         13.5            134.5
16-May-2015   20          3.5               23.5
17-May-2015   10          2.25             12.25

Upvotes: 1

Views: 219

Answers (5)

Amir Rahimi Farahani
Amir Rahimi Farahani

Reputation: 1590

If you want to put that data into a new table:

INSERT INTO Table1_Sum
(Date, Morning, EveningQty, Total)
SELECT Date, SUM(Morning), SUM(EveningQty), SUM(Total)
FROM Table1
GROUP BY Date

Upvotes: 1

Mureinik
Mureinik

Reputation: 311723

You are looking for a group by clause:

SELECT   `date`, SUM(morning), SUM(evening), SUM(total)
FROM     mytable
GROUP BY `date`

Upvotes: 5

Honza Haering
Honza Haering

Reputation: 812

SELECT `Date`, 
       SUM(Morning) AS Morning, 
       SUM(EveningQty) AS EveningQty, 
       SUM(Morning + EveningQty) AS Total
  FROM SomeTable
  GROUP BY `Date`

Upvotes: 2

user3419778
user3419778

Reputation: 866

I think this code will help you

INSERT INTO YOUR_NEW_TABLE (`date`, morning, evening, total ) 
SELECT date, sum(`morning`), sum(`evening`), sum(`Total`)
FROM YOUR_OLD_TABLE group by date

Upvotes: 0

Styphon
Styphon

Reputation: 10447

You can grab the data using

SELECT SUM(Morning) as Morning, SUM(EveningQty) as EveningQty, SUM(Total) as Total 
FROM table
WHERE Date = '$date'

So putting in 15-May-2015 for $date will give you 121, 13.50, 134.50

Upvotes: 2

Related Questions