CODEWITHSUNDEEP
phpmysqlsql
Sophia Jose
Sophia Jose

Reputation: 19

Multiple group by SQL data

I have a similar table in database:

id    desg        sr       per

1     desg-1      high     John Smith
2     desg-2      high     Peter Parker
3     desg-3      low      John Smith
4     desg-4      high     Mike
5     desg-5      high     Peter Parker
6     desg-6      low      John Smith

and now I want to sort out the data this way:

Name           count(desg)   high   low

John Smith     03            01     02
Peter Parker   02            00     02
Mike           01            01     00

what should be the SQL statement? may be its silly, but i really can't sort out the result. Please help me out.

Upvotes: 1

Views: 46

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You appear to want conditional aggregation. This is easy to do in MySQL, using sum() and boolean expressions:

select per, count(*) as num_desg,
       sum( sr = 'high' ) as num_high,
       sum( sr = 'low' ) as num_low
from t
group by per;

Note that the resulting numbers will not have a leading zero. I don't think that is desirable. If it is really needed, you can convert the number to a left-padded string.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

FWIW, seeing as you mentioned application code (PHP) I'd probably do something like this, and handle everything else in the presentation layer...

SELECT a.per
     , a.sr
     , SUM(COALESCE(b.sr = a.sr,0)) n
  FROM
     ( SELECT DISTINCT x.id, x.per, y.sr FROM my_table x, my_table y ) a
  LEFT
  JOIN my_table b
    ON b.id = a.id
   AND b.sr = a.sr
 GROUP
    BY per
     , sr
 ORDER
    BY per
     , sr;

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

Although mysql does not support pivot, but You can try to use condition aggregate function

CREATE TABLE T(
   ID INT,
   desg VARCHAR(50),
   sr VARCHAR(50),
   per VARCHAR(50)
);


INSERT INTO T VALUES (1,'desg-1','high','John Smith');
INSERT INTO T VALUES (2,'desg-2','high','Peter Parker');
INSERT INTO T VALUES (3,'desg-3','low','John Smith');
INSERT INTO T VALUES (4,'desg-4','high','Mike');
INSERT INTO T VALUES (5,'desg-5','high','Peter Parker');
INSERT INTO T VALUES (6,'desg-6','low','John Smith');

Query 1:

SELECT per,
    COUNT(desg),
    sum(CASE WHEN sr= 'high' THEN 1 else 0 END) high,
    sum(CASE WHEN sr= 'low' THEN 1 else 0  END) low
FROM T
GROUP BY per

Results:

|          per | COUNT(desg) | high | low |
|--------------|-------------|------|-----|
|   John Smith |           3 |    1 |   2 |
|         Mike |           1 |    1 |   0 |
| Peter Parker |           2 |    2 |   0 |

If you want to create the columns dynamically you can use dynamic pivot.

create dynamic create the SQL the execute it. 

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN sr= ''',
      sr,
      ''' THEN 1 else 0 END) ',
      sr
    )
  ) INTO @sql
FROM T;

SET @sql = CONCAT('SELECT per,
                     COUNT(desg), ', @sql, ' 
                   FROM T
                    GROUP BY per');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

sqlfiddle

Upvotes: 1

Related Questions