admin管理员组文章数量:1335386
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:
| Color | Size |
|---|---|
| Red | Small |
| Red | Medium |
| Blue | Small |
| Yellow | Large |
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:
| Color | Size |
|---|---|
| Red | Small |
| Red | Medium |
| Blue | Small |
| Yellow | Large |
Desired Query:
| Color | Number |
|---|---|
| Red | 2 |
| Blue | 1 |
| Yellow | 1 |
| Purple | 0 |
| Green | 0 |
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.
Share Improve this question edited Nov 20, 2024 at 0:38 Cur Fromsky asked Nov 20, 2024 at 0:23 Cur FromskyCur Fromsky 254 bronze badges 4 |1 Answer
Reset to default 3You 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.
本文标签： sqlQuery to obtain data summed from other queriesStack Overflow
版权声明：本文标题：sql - Query to obtain data summed from other queries - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1742388995a2465611.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
JOINwithGROUP BY. If not, then how would you know that GREEN is an option? – Tim Roberts Commented Nov 20, 2024 at 0:27