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

​x
 
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

 
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

 
fldYear  fldMonth   ConnPoints      NonConnPoints2020     January    13456             734562020     February   8345666           83756662020     March      734566            83662020     April      734569            3344692020     May        43456             134562020     June       33456             34562020     July       5345663           456632020     August     345661            756612020     September  345662            2456622020     November   345668            6456682020     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)

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