CODEWITHSUNDEEP
phpmysql
Nil Sagor
Nil Sagor

Reputation: 333

one table data insert into different table with sum and group by in mysql and php

I would like to show total purchase followed by date from purchase table to insert into total_purchase table as in date and buy as well as view 

my purchase table structure as below:

 id|companyName |purchase|date
  1|housedotcom |1300    |2016-1-1
  2|homedotcom  |1234    |2016-1-1
  3|gardendotco |1000    |2016-1-2
  4|landotcom   |999     |2016-1-2
  5|garagedotcom|5400    |2016-1-2
  6|homedotcom  |2600    |2016-1-2
  7|housedotcom |1900    |2016-1-2

  my total_purchase table as below

   id|date    |buy
    1|2016-1-1|2534
    2|2016-2-2|20890

I tried this into sql

INSERT INTO 'total_purchase'(date,buy) SELECT date, sum(purchase)
*FROM 'purchase' GROUP BY date;

And it showed in mysql result as I expected but when I tried insert new data into purchase table as same companyName with different date in mysql it says duplicate as well as in php coding sql it did not worked and showed this error

Error Processing RequestSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM purchaseGROUP BY date'

Any suggestions? Thanks in advance.

Upvotes: 0

Views: 954

Answers (2)

Wasiq Muhammad
Wasiq Muhammad

Reputation: 3118

For inserting value should be entered by just column by column.In Select you use * that return row more than one remove '*' 

INSERT INTO 'total_purchase'(date,buy) SELECT date, sum(purchase)
FROM 'purchase' GROUP BY date;

Upvotes: 0

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

remove * from your query to be:

INSERT INTO total_purchase (date,buy) SELECT date, sum(purchase)
FROM purchase GROUP BY date;

Upvotes: 1

Related Questions