Consider we are talking about gumballs, and I want to anize data via color.

The main query should return the columns color, size, number, is_chewy, etc. There are about 10 rows of data for this main query corresponding with the 10 colors of gumballs like red, blue, yellow, etc.

There are a lot of other queries that return all the relevant data, such as one that returns color and size. For example let's say this query returns something like red large, blue small, red medium, yellow small (the color and size are different columns). With this, I know that in the MAIN query, it should return RED 2, BLUE 1, YELLOW 1 for the color and number column respectively. Obviously there are only 3 colors so the remaining 7 colors should say 0. How would I use this query to write the MAIN query, where it should look something like RED 2, BLUE 1, YELLOW 1, GREEN 0, PURPLE 0, etc.? Should it be a brand new table/view? I don't think so because this data would change daily and I want to see what the count is for the current day. It is hard to explain what I am trying to get at but I am trying to group data by color in one query, then somehow put that into another table/query?

Query I already have:

Consider we are talking about gumballs, and I want to anize data via color.

The main query should return the columns color, size, number, is_chewy, etc. There are about 10 rows of data for this main query corresponding with the 10 colors of gumballs like red, blue, yellow, etc.

There are a lot of other queries that return all the relevant data, such as one that returns color and size. For example let's say this query returns something like red large, blue small, red medium, yellow small (the color and size are different columns). With this, I know that in the MAIN query, it should return RED 2, BLUE 1, YELLOW 1 for the color and number column respectively. Obviously there are only 3 colors so the remaining 7 colors should say 0. How would I use this query to write the MAIN query, where it should look something like RED 2, BLUE 1, YELLOW 1, GREEN 0, PURPLE 0, etc.? Should it be a brand new table/view? I don't think so because this data would change daily and I want to see what the count is for the current day. It is hard to explain what I am trying to get at but I am trying to group data by color in one query, then somehow put that into another table/query?

Query I already have:

Desired Query:

I would use group by but the thing is I want the desired query to always have all colors available, and if there is no data for that color to output 0.

• sql
• sql-server

• Please provide a minimal reproducible example with sample data, desired results and your attempt. (Not using images). – Dale K Commented Nov 20, 2024 at 0:24
• 1 One table presumably has "gumball orders" or "gumball preferences". Do you have another table that includes "all possible gumball colors"? If so, then it's just a JOIN with GROUP BY. If not, then how would you know that GREEN is an option? – Tim Roberts Commented Nov 20, 2024 at 0:27
• @TimRoberts This query is run every day and some days, there are no gumballs for some colors. Yes I can make that table with all possible colors as I know what all the colors are. – Cur Fromsky Commented Nov 20, 2024 at 0:42
• Do you need the results of your current query as they are as well as counted? Or just counted? It seems to be a simple case of joining your current results on your colour table and counting the rows? – Dale K Commented Nov 20, 2024 at 1:01

1 Answer 1

You need a separate table of colors, then left-join this table to it and group it.

SELECT
  c.Color,
  COUNT(cs.Size) AS Number     -- note the use of a column from the right side of the join
FROM Color c
LEFT JOIN ColorSize cs ON cs.Color = c.Color  -- left join everything else here
GROUP BY
  c.Color;

db<>fiddle

The same technique is often used with a calendar table containing every possible date.