如何从同一张表中查找具有不同where条件的多个记录的计数 [英] How to find count of multiple records with different where conditions from same table
本文介绍了如何从同一张表中查找具有不同where条件的多个记录的计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张这样的桌子
ID | cid |lightness | darkness | color
------|-------|-------------|--------------|---------
1 | 5 |10 | 20 | green
2 | 5 |10 | 08 | green
3 | 5 |10 | 10 | green
4 | 5 |20 | 05 | green
5 | 8 |10 | 20 | red
6 | 8 |10 | 16 | red
7 | 8 |33 | 20 | red
8 | 5 |10 | 10 | green
我想了解以下内容:
- 颜色为浅色10的记录数
- 颜色为暗20的记录数
所以输出应该是
Color | lightness | darkness | Total
---------|-------------|------------|---------
green | 4 | 1 | 5
red | 2 | 2 | 4
Total | 6 | 3 | 9
我已经尝试过下面的查询,但是没有带来正确的结果.
I've tried the query below but it doesn't bring the correct results.
Select color, sum(lightness), sum(darkness)
from colortable
where cid in (5,8)
and (lightness = 10 or darkness = 20)
Group by color;
推荐答案
将以下SQL保存为新查询, qryBaseCounts :
Save the following SQL as a new query, qryBaseCounts:
SELECT
sub.color,
sub.light_10,
sub.dark_20,
light_10+dark_20 AS light_plus_dark
FROM [
SELECT
color,
Sum(IIf(lightness=10,1,0)) AS light_10,
Sum(IIf(darkness=20,1,0)) AS dark_20
FROM colortable
WHERE
cid In (5,8)
AND (lightness=10
OR darkness=20)
GROUP BY color
]. AS sub;
然后,您可以在UNION查询中使用qryBaseCounts:
Then you can use qryBaseCounts in a UNION query:
SELECT
q1.color,
q1.light_10 AS lightness,
q1.dark_20 AS darkness,
q1.light_plus_dark AS [Total]
FROM qryBaseCounts AS q1
UNION ALL
SELECT
"Total",
Sum(q2.light_10)
Sum(q2.dark_20)
Sum(q2.light_plus_dark)
FROM qryBaseCounts AS q2;
这是第二次查询的Access 2007输出,使用您的 colortable 示例数据:
This is the Access 2007 output from that second query using your sample data for colortable:
color lightness darkness Total
green 4 1 5
red 2 2 4
Total 6 3 9
这篇关于如何从同一张表中查找具有不同where条件的多个记录的计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文