How to remove duplicate values from an sql query

Question

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!

Do you want to sum the values or you want them as 2000 +1900 ? — Dekel
– Dekel, Commented Nov 6, 2016 at 16:04
@Dekel thanks Your comment is loyal .. i upvote your answer .. (the meaning of the answer is practically the same) — ScaisEdge
– ScaisEdge, Commented Nov 6, 2016 at 16:14
well, not exactly the same because he wanted a sum (and not a string [like in his example]). you got my vote here as well :) — Dekel
– Dekel, Commented Nov 6, 2016 at 16:15

Dekel · Accepted Answer · 2016-11-06 16:03:56Z

2

You can use the group_concat function:

$query = "SELECT useraccount.Username, tariff.Name as tariffs, 
GROUP_CONCAT(energyconsumption.ElecEnergy SEPARATOR ' +')
    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";

answered Nov 6, 2016 at 16:03

Dekel

62.9k12 gold badges109 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2016-11-06 17:16:44Z

1

You should use a sum and a group by

$query = "SELECT useraccount.Username as Username, tariff.Name as TariffName, 
sum(energyconsumption.ElecEnergy) as ElecConsump
    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 )

edited Nov 6, 2016 at 17:16

answered Nov 6, 2016 at 16:03

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

8 Comments

Mahbs Over a year ago

Brilliant! Thank you so much! Could i ask you one last question. I tested the query, and im getting an error. This is the rest of the code after the query: $result = mysqli_query($conn,$query); $r = array(); if($result->num_rows){ while($row = mysqli_fetch_array($result)){ array_push($r, array( 'Username' => $row['Username'], 'TariffName' => $row['tariffs'], 'ElecConsump' => $row['ElecEnergy'] )); } } echo json_encode(array('results' => $r)); The error is in this line: if($result->num_rows)

Mahbs Over a year ago

@Dekel will do. Thanks!

Mahbs Over a year ago

@scaisEdge Done. Thank you once again for your help!

ScaisEdge Over a year ago

@Mahbs . if you have another question post a proper new question .. so all the SO user can help you

Mahbs Over a year ago

@scaisEdge Will do! Thank you once again!

|

Collectives™ on Stack Overflow

How to remove duplicate values from an sql query

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related