i have one table with a single column having three values a,b and c. Current snapshot of the table is
Table name:- tblTest
Values
tblColumn
a
a
a
b
b
b
b
c
c
i need to get the output exactly as
A B C
3 4 2
Add a comment
|
3 Answers
select sum(tblColumn = 'a') as A,
sum(tblColumn = 'b') as B,
sum(tblColumn = 'b') as C
from tblTest
Comments
SELECT SUM(CASE WHEN colName = 'a' THEN 1 ELSE 0 END) as A,
SUM(CASE WHEN colName = 'b' THEN 1 ELSE 0 END) as B,
SUM(CASE WHEN colName = 'c' THEN 1 ELSE 0 END) as C
FROM tableName
another technique is by using PreparedStatement, this is very good if you have multiple unknown number group of values, eg, a,b,c,d,e,f,g,h,...
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(IF(tblColumn = ''',
tblColumn,
''', 1, 0)) AS ',
tblColumn
)
) INTO @sql
FROM
Table1;
SET @sql = CONCAT('SELECT ', @sql, ' FROM Table1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SQLFiddle Demo
3 Comments
Mihai Stancu
Note the values are lowercase. When comparing "case insensitive" the results will be the same either way. But when comparing "case sensitive" you have to use lowercase values too.
John Woo
@MihaiStancu yep, anyway i just added prepared statement.
Try this
select tblColumn, Count(*) from tblTest group by tblColumn
2 Comments
Mihai Stancu
The result set won't be structured as requested.
RNH
Agree, RedFilter... we can do without Distinct().. :) Changed.