Need to find count of profit and loss and no data using select subquery-CodePudding

I have the following table

Years Months Credit  Debit  ProfitandLoss Symbol
2019    Jan  10000   2000       1000         P
2019    Aug  8000    1000      -10922        L
2019    May  5000    3000       2000         P
2020    Feb  10000   5000       800          P
2020    Apr  1000    6000        0           N
2020    Oct  2000    1000       2000         P
2021    Jan  6000    8000       -600         L
2021    Mar  2000    3000       1400         P
2021    Nov  2000    2000        0           N

Here I need to calculate total credit, total debit, total profit and loss and total count of profit, total count of loss, total count of nothing in one result table.

I have tried this but cannot get CountOfProfit, CountOfLoss and CountNothing according to years.

select Years,
      SUM(credit) as [Total Credit],
      SUM(debit) as totaldebit,
      COUNT(Symbol) as totalcredit,
      (select COUNT(Symbol) from Yearly where Symbol='P') as CountofProfit,
      (select  COUNT(Symbol) from Yearly where Symbol='L') as CountofLoss, 
      (select COUNT(Symbol) from Yearly where Symbol='N')as CountNothing
from Yearly 
group by Years

My result table should be like

Years TotalCredit TotalDebit TotalProfitandLoss CountOfProfit CountofLoss CountofNothing
2019   23000       7000       -7022                 2           1             0
2020   13000       12000       2800                 2           0             1         
2021   10000       13000       800                  1           1             1

CodePudding user response：

You need use conditional aggregation. This is achieved with a CASE expression:

SELECT Years,
       SUM(Credit) AS TotalCredit,
       SUM(Debit) AS TotalDebit,
       SUM(ProfitandLoss) AS TotalProfitAndLoss,
       COUNT(CASE Symbol WHEN 'P' THEN 1 END) AS Profits,
       COUNT(CASE Symbol WHEN 'L' THEN 1 END) AS Losses,
       COUNT(CASE Symbol WHEN 'N' THEN 1 END) AS Nothings
FROM (VALUES(2019,'Jan',10000,2000, 1000 ,'P'),
            (2019,'Aug',8000 ,1000,-10922,'L'),
            (2019,'May',5000 ,3000, 2000 ,'P'),
            (2020,'Feb',10000,5000, 800  ,'P'),
            (2020,'Apr',1000 ,6000,  0   ,'N'),
            (2020,'Oct',2000 ,1000, 2000 ,'P'),
            (2021,'Jan',6000 ,8000, -600 ,'L'),
            (2021,'Mar',2000 ,3000, 1400 ,'P'),
            (2021,'Nov',2000 ,2000,  0   ,'N'))V(Years,Months,Credit,Debit,ProfitandLoss,Symbol)
GROUP BY Years
ORDER BY Years;