How to find the number of multiple records with different conditions of the same table

I have a table such as this

``````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
```
```

I want to find out the following:

• Count of records where color has lightness 10
• Count of records where color has darkness 20

So the output should be

``````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;
```
```

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;
```
```

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;
```
```

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
```
```