I have a sample table that stores information as follows: The two sub-systems are mini1 and mini2
| System | Date | Sub System | Count |
|---|---|---|---|
| primary | 2022-05-11 | mini1 | 103 |
| secondary | 2022-05-11 | mini2 | 100 |
| secondary | 2022-05-11 | mini1 | 10 |
| backup | 2022-05-11 | mini2 | 95 |
| backup | 2022-05-11 | mini1 | 11 |
| primary | 2022-05-11 | mini2 | 15 |
| primary | 2022-04-01 | mini1 | 0 |
| secondary | 22022-04-01 | mini2 | 0 |
| secondary | 2022-04-01 | mini1 | 100 |
| backup | 2022-04-01 | mini2 | 0 |
| backup | 2022-04-01 | mini1 | 110 |
| primary | 2022-04-01 | mini2 | 0 |
I want to get the latest information on each system in a format such as follows, where only the latest date is considered.
| System | Latest Date | mini1 Count | mini 2 Count |
|---|---|---|---|
| primary | 2022-05-11 | 103 | 15 |
| secondary | 2022-05-11 | 10 | 100 |
| backup | 2022-05-11 | 11 | 95 |
Is this possible through sql?
CodePudding user response：
Do a GROUP BY. Use case expressions to do conditional aggregation.
select System,
max(Date) LatestDate,
sum(case when SubSystem = 'mini1' then count else 0 end) Mini1Count,
sum(case when SubSystem = 'mini2' then count else 0 end) Mini2Count
from tablename
group by System