Combining multiple SQL select statements as columns

Combining multiple select statements as a columns, is not giving the desired output. What I’m missing here? Group by clause is not working as expected. Do I need to use GroupBy for outer Select as well.?

SQL Query:

use [Test_DB]
SELECT  a.fldYear,a.fldMonth, a.ConnPoints,b.NonConnPoints from 
(Select  DATEPART(YEAR, e.TestDate) as [fldYear], DATENAME(month,e.TestDate) as [fldMonth], count(e.equipid) as [ConnPoints] from dbo.equip e 
where e.pID<>0 and e.TestDate between '01-01-2020' and '12-31-2020' 
Group By  DATEPART(year,e.TestDate), DATENAME(month,e.TestDate), DATEPART(month,e.TestDate)) as a,

(Select  DATEPART(YEAR, e.TestDate) as [fldYear], DATENAME(month,e.TestDate) as [fldMonth], count(e.equipid) as [NonConnPoints] from dbo.equip e 
where e.PID=0 and e.TestDate between '01-01-2020' and '12-31-2020' 
Group By  DATEPART(year,e.TestDate), DATENAME(month,e.TestDate), DATEPART(month,e.TestDate)) as b

Current Output: Showing data of 144 rows for 2020

fldYear  fldMonth     ConnPoints      NonConnPoints
    2020     January    13456             73456
    2020     February   8345666           8375666
    2020     January    13456             8366
    2020     April      734569            334469
    2020     February   8345666           13456
    2020     June       33456             3456
    2020     April      734569            45663

Output I’m looking: Should show data of 12 months for the year 2020

fldYear  fldMonth   ConnPoints      NonConnPoints
2020     January    13456             73456
2020     February   8345666           8375666
2020     March      734566            8366
2020     April      734569            334469
2020     May        43456             13456
2020     June       33456             3456
2020     July       5345663           45663
2020     August     345661            75661
2020     September  345662            245662
2020     November   345668            645668
2020     December   534566            538866

Answer

Do you just want conditional aggregation:

select year(e.TestDate), month(e.TestDate),
       sum(case when e.pID <> 0 then 1 else 0 end) as ConnPoints,
       sum(case when e.pID = 0 then 1 else 0 end) as NonConnPoints
from dbo.equip e 
where e.TestDate between '2020-01-01' and '2020-12-31'
group by year(e.TestDate), month(e.TestDate)
order by year(e.TestDate), month(e.TestDate)

Advertisement

Answer