CODEWITHSUNDEEP
mysqldatabasecodeignitercount
user1682073
user1682073

Reputation: 47

count field database by group

table order

id_order | date    | id_product | id_size | id_gender | name |
   1     |2012-1-1 |     1      |    1    |    1      | john |
   2     |2012-1-1 |     1      |    2    |    1      | mayer|
   3     |2012-1-1 |     2      |    1    |    1      | chris|

tabel product

id_shirt |  name   |
   1     |  jeans  |
   2     |  shirt  |
   3     |  jacket |

tabel size

id_size  |  size  |
   1     |   S    |
   2     |   M    |
   3     |   L    |
   4     |   XL   |

i would produce like this where gender male.

name  |  S  |  M  |  L  |  XL  |
jeans |  2  |     |     |      |
shirt |     |  1  |     |      |

Please query in codeigniter code .

Upvotes: 0

Views: 91

Answers (1)

John Woo
John Woo

Reputation: 263943

SELECT  b.name,
        SUM(CASE WHEN c.size = 'S' THEN 1 ELSE 0 END) S,
        SUM(CASE WHEN c.size = 'M' THEN 1 ELSE 0 END) M,
        SUM(CASE WHEN c.size = 'L' THEN 1 ELSE 0 END) L,
        SUM(CASE WHEN c.size = 'XL' THEN 1 ELSE 0 END) XL
FROM    orderTB a
        INNER JOIN productTB b
            ON a.id_product = b.id_shirt
        INNER JOIN sizeTB c
            ON a.id_size = c.id_size
GROUP BY b.name

Alternatively, If you have unknown values of sizes, a Dynamic SQL is much more preferred, eg

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN c.size = ''',
      size,
      ''' THEN 1 ELSE 0 END) AS ',
      size
    )
  ) INTO @sql
FROM sizeTB;

SET @sql = CONCAT('SELECT  b.name, ', @sql, ' 
                  FROM    orderTB a
                          INNER JOIN productTB b
                              ON a.id_product = b.id_shirt
                          INNER JOIN sizeTB c
                              ON a.id_size = c.id_size
                  GROUP BY b.name');

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

Upvotes: 3

Related Questions