I'm trying to make a query and i'm having a bad time with one thing. Suppose I have a table that looks like this:
| id | Sample | Species | Quantity | Group |
|---|---|---|---|---|
| 1 | 1 | AA | 5 | A |
| 2 | 1 | AB | 6 | A |
| 3 | 1 | AC | 10 | A |
| 4 | 1 | CD | 15 | C |
| 5 | 1 | CE | 20 | C |
| 6 | 1 | DA | 13 | D |
| 7 | 1 | DB | 7 | D |
| 8 | 1 | EA | 6 | E |
| 9 | 1 | EF | 4 | E |
| 10 | 1 | EB | 2 | E |
In the table I filter to have just 1 sample (but i have many), it has the species, the quantity of that species and a functional group (there are only five groups from A to E). I would like to make a query to group by the samples and make columns of the counts of the species of certain group, something like this:
| Sample | N_especies | Group A | Group B | Group C | Group D | Group E |
|---|---|---|---|---|---|---|
| 1 | 10 | 3 | 0 | 2 | 2 | 3 |
So i have to count the species (thats easy) but i don't know how to make the columns of a certain group, can anyone help me?
CodePudding user response：
Conditional aggregation should do it:
SELECT
Sample,
COUNT(*) AS [Count Rows],
COUNT(CASE WHEN [Group] = 'A' THEN 1 END) AS [Group A],
COUNT(CASE WHEN [Group] = 'B' THEN 1 END) AS [Group B],
COUNT(CASE WHEN [Group] = 'C' THEN 1 END) AS [Group C],
COUNT(CASE WHEN [Group] = 'D' THEN 1 END) AS [Group D],
COUNT(CASE WHEN [Group] = 'E' THEN 1 END) AS [Group E]
FROM t
GROUP BY Sample
CodePudding user response：
You can use PIVOT :
Select a.Sample,[A],[B],[C],[D],[E], [B] [A] [C] [D] [E] N_especies from
(select t.Sample,t.Grp from [WS_Database].[dbo].[test1] t) t
PIVOT (
COUNT(t.Grp)
for t.Grp in ([A],[B],[C],[D],[E])
) a