Mahbs Mahbs - 27 days ago 12
SQL Question

How to remove duplicate values from an sql query

I want to know how to remove duplicate values from the output of an sql query.

This is the sql query:

$query = "SELECT useraccount.Username, tariff.Name as tariffs,
energyconsumption.ElecEnergy, useraccount.Username as User
FROM useraccount
INNER JOIN tariff
ON useraccount.tariffs = tariff.id
INNER JOIN energyconsumption
ON energyconsumption.User = useraccount.id
WHERE Date = CURRENT_DATE";


This is the output of that query:

{"results":[{"Username":"absc868","TariffName":"s1","ElecConsump":"2000"},
{"Username":"absc868","TariffName":"s1","ElecConsump":"1900"}]}


As you can see, the query filters out data where the data matches todays date. We have 2 outputs for the same user. The value of the tariff name and username are the same,but the energy consumption value is different which is fine.

I want to achieve the following output:

= {"results":[{"Username":"absc868","TariffName":"s1","ElecConsump":"2000 +1900"}


= {"results":[{"Username":"absc868","TariffName":"s1","ElecConsump":"3900"}


Could someone point me to the direction in how I can achieve this?

Thank you in advance to those who read the post and contributed!

Answer

You should use a sum and a group by

$query = "SELECT useraccount.Username, tariff.Name as tariffs, 
sum(energyconsumption.ElecEnergy)
    FROM useraccount
    INNER JOIN tariff 
ON useraccount.tariffs = tariff.id
INNER JOIN energyconsumption
ON energyconsumption.User = useraccount.id
WHERE Date = CURRENT_DATE
GROUP BY useraccount.Username, tariff.Name as tariffs";

(you have some difference between table column name alias and object attribute name )

Comments