CODEWITHSUNDEEP
mysqlsqlgroup-by
Beadyyy
Beadyyy

Reputation: 37

SQL Group by 2 column

I want to display once in multiple rows with the 2 GROUP_BY

My table is like this 

    +----+----------+-------+
    | ID | BRAND |   TYPE    | 
    +----+----------+-------+
    |  1 | A     |  Clothes  | 
    |  2 | A     |  Hat      | 
    |  3 | A     |  Hat      | 
    |  4 | A     |  Hat      | 
    |  5 | B     |  Jeans    | 
    |  6 | B     |  Jeans    | 
    |  7 | B     |  Hat      |
    |  8 | C     |  Clothes  |
    |  9 | C     |  Jeans    |
    | 10 | C     |  Jeans    |
    | 11 | C     |  Hat      | 
    +----+-------+-----------+

And my query like this 

SELECT brand, 
       type, 
       COUNT(*) AS total
FROM store  
GROUP BY brand, type

When I run that query the result is

        -----------------------------
        | BRAND |   TYPE    |  TOTAL |
        -----------------------------
        | A     |  Clothes  |   1    |
        | A     |  Hat      |   3    | 
        | B     |  Jeans    |   2    |
        | B     |  Hat      |   1    |
        | C     |  Clothes  |   1    |
        | C     |  Jeans    |   2    |
        | C     |  Hat      |   1    |
        ------------------------------

Thats not the result I expect. I just wanna display once Brand and with the following Type also display once if there are multiple same value.

UPDATE: expect result

        -------------------------------------------
        | BRAND |   TYPE                |  TOTAL |
        -------------------------------------------
        | A     |  Clothes, Hat         |   4    |
        | B     |  Jeans, Hat           |   3    |
        | C     |  Clothes, Jeans, Hat  |   4    |
        -------------------------------------------

Upvotes: 1

Views: 231

Answers (3)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • Using Group_Concat function with Distinct clause, you can get all different and unique type(s) in a comma separated string, under a brand group.
  • Use Sum function to calculate the total

Try the following:

SELECT brand, 
       GROUP_CONCAT(DISTINCT type) As type, 
       SUM(total) AS total
FROM store  
GROUP BY brand

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Your description suggests me to do conditional aggregation :

SELECT brand, 
       COUNT(DISTINCT CASE WHEN TYPE = 'Clothes' THEN TYPE END) AS Clothes,
       COUNT(DISTINCT CASE WHEN TYPE = 'Hat' THEN TYPE END) AS Hat,
       COUNT(DISTINCT CASE WHEN TYPE = 'Jeans' THEN TYPE END) AS Jeans
FROM store s
GROUP BY brand;

If you want to one row with multiple types then you can use GROUP_CONCAT :

SELECT brand, GROUP_CONCAT(DISTINCT TYPE) As TYPE, COUNT(total) AS total
FROM store s  
GROUP BY brand;

Upvotes: 0

Walter_Luckystike
Walter_Luckystike

Reputation: 1

with this you get olny one type per brand

SELECT brand, Max( type) AS type, COUNT(*) AS total FROM store
GROUP BY brand

Upvotes: 0

Related Questions