All geek questions in one place

Number of SQL instances for specific categories of rows

I have this table with some data:

table ColorsFlavors id | name | color | flavor -------------------------- 1 | n1 | green | lemon 2 | n2 | blue | strawberry 3 | n3 | red | lemon 4 | n4 | green | lemon 5 | n5 | green | mango 6 | n6 | red | chocolate 7 | n7 | white | lemon 8 | n8 | blue | mango 9 | n9 | green | chocolate

I want to make an SQL query (or queries?) That allows me to get the total number of rows with each color and the total number of rows with each flavor.

Something like that:

 colors | occurrences -------------------- green | 4 blue | 2 red | 6 white | 1 flavor | occurences ---------------------- lemon | 4 strawberry| 1 mango | 2 chocolate | 2

Well, if I have a predefined list of colors and flavors to choose from, so that colors / flavors that are not found in the data table get 0 counts?

 colors | occurrences -------------------- green | 4 blue | 2 red | 6 white | 1 black | 0 flavor | occurences ---------------------- lemon | 4 strawberry| 1 mango | 2 chocolate | 2 cherry | 0

So what will be the SQL queries to receive them?

+1
source share
1 answer

To make all colors in the ColorsFlavors table

 Select cf.Color, Count(*) From ColorsFlavors cf Group By cf.Color

If you have a predefined list in the table (which I will call Colors) and you want to include zeros:

 Select c.Color, Coalesce(Count(*), 0) From Colors c Left Outer Join ColorsFlavors cf On c.Color = cf.Color Group By c.Color

If you have a predefined list that someone typed

 Select c.Color, Coalesce(Count(*), 0) From ( Select 'green' As Color Union All Select 'blue' Union All Select 'red' Union All Select 'white' Union All Select 'black' ) c Left Outer Join ColorsFlavors cf On c.Color = cf.Color Group By c.Color

With this, you can perfect the aromas!

+4
source

Source: https://habr.com/ru/post/1447852/

More articles:


All Articles