How can I sum the same values with MySQL? This is my table for example:
--Id---------Name--
--1 ---------Value1
--2 ---------Value3
--3 ---------Value2
--4 ---------Value2
--5 ---------Value3
--6 ---------Value2
Now I should make a SQL query that sums the amount of same values, like:
SELECT SUM(IF(Name = 'Value1', SUM, 0)) AS Value1_total
, SUM(IF(Name = 'Value2', SUM, 0)) AS Value2_total
, SUM(IF(Name = 'Value3', SUM, 0)) AS Value3_total
Since you want the 3 sums returned in a single query, you're on the right track, query-wise, but it needs to be:
SELECT SUM(IF(Name = 'Value1',1,0)) AS Value1_total
^--- change 'SUM' to 1
but the great issuer is that this is not very extensible. You need to have this SUM() construct for every field you want summed in this way. It'd be more efficient to use a standard grouping query and then build this "horizontal" structure in your client-side code:
SELECT Name, COUNT(id)
FROM yourtable
WHERE (Name IN ('Value1', 'Value2', 'Value3', ...))
GROUP BY Name
Try:
SELECT name, COUNT(name) amount FROM table
GROUP BY name;
This will give you the actual value of the name field and the number of times its encountered.
$query = "SELECT name, COUNT(name) amount FROM table
GROUP BY name";
if (($result = mysqli_query($query))) {
$data = array();
while (($row = mysql_fetch_array($result,MYSQL_ASSOC))) {
$data[$row['name']] = $row['amount'];
}
// then you can
if (isset($data['Value1'])) {
echo $data['Value1'];
}
}
You may also try the following:
SELECT a.s Value1_Total, b.s Value2_Total, c.s Value3_Total FROM t2,
(SELECT COUNT(name) s FROM t2 WHERE name = 'AAA') a,
(SELECT COUNT(name) s FROM t2 WHERE name = 'BBB') b,
(SELECT COUNT(name) s FROM t2 WHERE name = 'CCC') c;
Edit: This should work, however it is very inefficient. You could probably use PIVOT if you want to make it more efficient.
select
(select count(1) from table where name='Value1') as Value1_total
(select count(1) from table where name='Value2') as Value2_total
(select count(1) from table where name='Value3') as Value3_total
from
dual;
