CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bycount
apples-oranges
apples-oranges

Reputation: 987

Get counts of each sub-group within main

I would like to get the counts of each sub-group that appear in the main group.

Given the following:

| Clothing    | Colour
-----------------------
| Shirt       | black   
| Shirt       | black  
| Shirt       | black
| Shirt       | yellow 
| Shoes       | yellow
| Shoes       | yellow
| Shoes       | black

My desired output would be:

| A                    | B
---------------------------
| Shirt_Blacks         | 3 
| Shirt_Yellows        | 1
| Shoes_Blacks         | 1
| Shoes_Yellows        | 2

EDIT: Output format 2:

| Clothing       | Black | Yellow
-----------------------------------
| Shirt          | 3     | 1
| Shoes          | 1     | 2

Tried the following with no luck:

SELECT COUNT(Colour) 
FROM [randomtable]
Group By Clothing

Upvotes: 1

Views: 42

Answers (3)

jyoti ranjan
jyoti ranjan

Reputation: 1

SELECT Clothing+'_'+Colour A, COUNT(*) B
    FROM [randomtable]
GROUP BY Clothing+'_'+Colour

select * from [randomtable]
    pivot
(count(colour) for colour in (black,yellow))AS piv

Upvotes: 0

GMB
GMB

Reputation: 222492

Use aggregation:

SELECT CONCAT(Clothing, '_', Colour) A, COUNT(*) B
FROM mytable
GROUP BY Clothing, Colour

Demo on DB Fiddle:

A            |  B
:----------- | -:
Shirt_black  |  3
Shoes_black  |  1
Shirt_yellow |  1
Shoes_yellow |  2

If you are looking to pivot the resultset, then:

SELECT 
    Clothing,
    SUM(CASE WHEN Colour = 'Black' THEN 1 ELSE 0 END) Black,
    SUM(CASE WHEN Colour = 'Yellow THEN 1 ELSE 0 END) Yellow
FROM mytable
GROUP BY Clothing

Upvotes: 1

forpas
forpas

Reputation: 164099

You can group by concat(clothing, '_', colour):

SELECT
  concat(clothing, '_', colour) A, 
  COUNT(*) B
FROM [randomtable]
Group By concat(clothing, '_', colour)

Upvotes: 1

Related Questions