CODEWITHSUNDEEP
mysqlsqlpivot
tina
tina

Reputation: 312

How to get the output in mysql the way i need is give below

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

Upvotes: 2

Views: 109

Answers (3)

RNH
RNH

Reputation: 93

Try this

select tblColumn, Count(*) from tblTest group by tblColumn

Upvotes: 2

John Woo
John Woo

Reputation: 263723

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

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

select sum(tblColumn = 'a') as A,
    sum(tblColumn = 'b') as B,
    sum(tblColumn = 'b') as C
from tblTest

SQL Fiddle Example

Upvotes: 4

Related Questions