CODEWITHSUNDEEP
sqlsql-server
user716255
user716255

Reputation: 443

Need to count rows from two columns

I am trying to count the number of times [Tracking id] & [Tracking ID1] appear in a table.

enter image description here

The result should look like this.

enter image description here

This works great when I use a group by function.

select [Group], datename(month,[date]) as Month, count([Tracking ID]) + count([Tracking ID1]) as [Number of Record]
from   [Table]
group by    
    [Group],
    datename(month,[date])

I need to use a pivot function. How do I modify the code below to get the desired results

select * 
from 
    (
select  
    [Group], datename(month,[date]) as Month, (count([Tracking ID])over () + count([Tracking ID1]) over () ) as [Number of Record]
From    
    [Table]
) sourcetbl
pivot
    (count([Number of Record])  
for [Month] in ([June], [July])
) as pivotTable;

Currently, my query returns 2 instead of 4.

enter image description here

Upvotes: 0

Views: 83

Answers (2)

IB van Schalkwyk
IB van Schalkwyk

Reputation: 81

This should work based on the information provided:

SELECT
    [Group],
    ISNULL([June], 0) AS [June],
    ISNULL([July], 0) AS [July]
FROM
(
    SELECT
        [Group],
        DATENAME(MONTH, [Date]) AS [Date],
        COUNT(Tracking_ID) + COUNT(Tracking_ID1) AS NumberOfRecords
    FROM [Table]
    GROUP BY [Group], [Date]
) AS D
PIVOT
(
    MAX(NumberOfRecords)
    FOR [Date] IN 
    (
        [June],
        [July]
    )
) AS PIV

It seems the OVER() parts in your query were causing the COUNT functions to not behave as aggregate functions, thus your query works without a GROUP BY where in this case it needs to have a GROUP BY.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Just use conditional aggregation:

select [Group],
       sum(case when month([date]) = 6 then 1 else 0 end) as jun,
       sum(case when month([date]) = 7 then 1 else 0 end) as jul
from  [Table]
group by [Group];

Upvotes: 2

Related Questions