CODEWITHSUNDEEP
phpmysqlsql
Zarkaylia
Zarkaylia

Reputation: 71

SQL count total of values in column where other column value occurs multiple times

I have 2 tables as example (columns D and F are irrelevant to this and just for showing there are more columns in the real table):

| A | B | C |
|123| 1 | 3 |
|456| 2 | 1 |
|123| 3 | 2 |

and

| a | D | E | F    |
|123| & |Ana|apple |
|456| * |Eva|pear  |
|789| % |Ola|orange|

"A" and "a" symbolize values linking the tables.

I'd like to on my page display a table that adds the total of values in column C where the rows in A have several of same value.

This means that the example above for row A = 123, which occurs in that column twice, would be a total of 5 displayed in a single row (values from column C in both rows summed up to 5. 2 + 3 = 5). For A = 456 the total would equal 1 as 456 occurs only once in column A and at that occurance has the value 1 in column C, as above displays.

The end result desired is a table like:

| a | E | D | c |
|123|Ana| & | 5 |
|456|Eva| * | 1 |

Where "c" symbolizes the total I want.

Is there a good way to do this in an SQL query which allows for simple output with help of PHP for my page?

Upvotes: 1

Views: 482

Answers (3)

vaibhav pancholi
vaibhav pancholi

Reputation: 51

i got it

select a,sum(c) from demo2 where a = a group by a;

https://i.sstatic.net/ZyEqU.png

Upvotes: 0

Ben Dadsetan
Ben Dadsetan

Reputation: 1565

if I understand correctly what you are looking for, you likely want to use some SQL functions that help in such cases.

select a, E, D, sum(C) as c 
from t1 INNER JOIN t2 on t1.A = t2.a 
group by a, E, D

sum() will sum up the values of C for each unique occurence of a, E and D

Upvotes: 1

Eye webdesign
Eye webdesign

Reputation: 46

you should write something like

select A, sum(C) from first_table group by A

If you would combine it with values of the second table, you should use join

Upvotes: 0

Related Questions