0

I have two tables Table1 and Table2.

Table1 contains records of users and products they have purchased.

Table2 contains a list of the products mentioned in Table1 with their price

Example of the table explained above:

tables

I would like to join these two tables in a query that takes the sum of all the sum value of all the purchases made by each user and display that than create a JSON that looks like this:

[{"user":"Jack","sumValue":"4.50"},
{"user":"Jake","sumValue":" 4.00 "},{"user":"Mary","sumValue":" 8.50 "}]

I understand how to create the JSON array but don't understand how the SQL can be written

$sql =  "      What I am missing                  ";

if ($result = mysqli_query($con, $sql))
John
  • 965
  • 8
  • 16

1 Answers1

0
SELECT User, SUM(Price) as sumValue 
FROM Table1
LEFT JOIN Table2 USING(Product)
GROUP BY User
Dharman
  • 30,962
  • 25
  • 85
  • 135
  • It seems to not be working, Table1 doesn't have a column named Price so why are you putting it before Table1? – John Jul 15 '19 at 20:39
  • I join both tables together. It will take that column from whichever table it exists in. What error are you getting? – Dharman Jul 15 '19 at 20:40
  • Could you show us in the question the full code you are using now? There must be something else that is wrong. – Dharman Jul 15 '19 at 20:45
  • 1
    My apologies it was a syntax error in my php, thank you for your help – John Jul 15 '19 at 20:48
  • For future reference: [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Jul 15 '19 at 20:49
  • Is there anyway to also have the sql calculate the sum of all values and display it at the end of the json ? Like “totalSum”:” “ ? – John Jul 15 '19 at 23:25