CODEWITHSUNDEEP
sql-serverdatesql-server-2008-r2
Rygar
Rygar

Reputation: 65

SQL - Count number of rows based on date and when next three columns are same

I need to create a report. In this query I have four columns being selected

I need to count how many rows exist where the date is different but the ID, GroupNumber, Type are the same. The date doesn't necessarily need to be displayed

Here is the code I have so far

    Select DISTINCT CAST(MeasureDate AS Date) AS MeasureDate,
    StudyNumber, GroupNumber, [Type]
    From tUserEquipMeas

    Group by StudyNumber, MeasureDate, GroupNumber, [type]

Upvotes: 0

Views: 1873

Answers (3)

Matt
Matt

Reputation: 14341

It would be good to include some sample data and expected result based on that data. Because it is unclear for example if you want the count only for each combination of SudyNumber, GroupNumber, and Type or if you want the distinct count of days for each of combination and then the aggregate count of that. Also it is not clear if you want any details on the combination.

@scsimon has a good answer if you simply want the overall count of all unique combinations of StudyNumber, GroupNumber, Type, And Date.

If you simply want the count with the combination your query could be modified as follows:

Select COUNT( DISTINCT CAST(MeasureDate AS Date)) AS CountOfDistinctMeasureDates,
StudyNumber, GroupNumber, [Type]
From tUserEquipMeas
Group by StudyNumber, MeasureDate, GroupNumber, [type]

And if you want the details of each combination with the overall count all in the same query because you tagged a sql-server capable of using window functions here is one way:

SELECT
    CAST(MeasureDate AS DATE)
    ,Studeynumber
    ,GroupNumber
    ,Type
    ,COUNT(*) as NumberOfTotalRecordsInGroup
    ,COUNT(*) OVER (PARTITION BY Studeynumber, GroupNumber, Type) AS NumberOfDistinctMeasureDatesForGroup
FROM
    @Table
GROUP BY
    CAST(MeasureDate AS DATE)
    ,Studeynumber
    ,GroupNumber
    ,Type

Here is some test data for you:

DECLARE @Table AS TABLE (MeasureDate DATETIME, Studeynumber INT, GroupNumber INT, Type CHAR(1))
INSERT INTO @Table (MeasureDate, Studeynumber, GroupNumber, Type)
VALUES
    (GETDATE(), 123456, 1, 'A')
    ,(DATEADD(HOUR,-2,GETDATE()), 123456, 1, 'A')
    ,(DATEADD(HOUR,-4,GETDATE()), 123456, 1, 'A')
    ,(GETDATE() - 1, 123456, 1, 'A')
    ,(DATEADD(HOUR,-2,GETDATE() - 1), 123456, 1, 'A')
    ,(DATEADD(HOUR,-4,GETDATE() - 1), 123456, 1, 'A')
    ,(GETDATE() - 2, 123456, 1, 'A')
    ,(DATEADD(HOUR,-2,GETDATE() - 2), 123456, 1, 'A')
    ,(DATEADD(HOUR,-4,GETDATE() - 2), 123456, 1, 'A')
    ,(GETDATE(), 123456, 2, 'A')
    ,(DATEADD(HOUR,-2,GETDATE()), 123456, 2, 'A')
    ,(DATEADD(HOUR,-4,GETDATE()), 123456, 2, 'A')
    ,(GETDATE() - 1, 123456, 2, 'A')
    ,(DATEADD(HOUR,-2,GETDATE() - 1), 123456, 2, 'A')
    ,(DATEADD(HOUR,-4,GETDATE() - 1), 123456, 2, 'A')
    ,(GETDATE() - 2, 123456, 2, 'A')
    ,(DATEADD(MINUTE,-2,GETDATE() - 2), 123456, 2, 'A')
    ,(DATEADD(MINUTE,-3,GETDATE() - 2), 123456, 2, 'A')
    ,(DATEADD(MINUTE,-4,GETDATE() - 2), 123456, 2, 'A')
    ,(DATEADD(MINUTE,-5,GETDATE() - 2), 123456, 2, 'A')
    ,(DATEADD(MINUTE,-6,GETDATE() - 2), 123456, 2, 'A')
    ,(GETDATE() - 3, 123456, 2, 'A')
    ,(GETDATE(), 123456, 1, 'B')
    ,(DATEADD(HOUR,-2,GETDATE()), 123456, 1, 'B')
    ,(DATEADD(HOUR,-4,GETDATE()), 123456, 1, 'B')
    ,(GETDATE() - 1, 123456, 1, 'B')
    ,(DATEADD(HOUR,-2,GETDATE() - 1), 123456, 1, 'B')
    ,(DATEADD(HOUR,-4,GETDATE() - 1), 123456, 1, 'B')
    ,(GETDATE() - 2, 123456, 1, 'B')
    ,(DATEADD(HOUR,-2,GETDATE() - 2), 123456, 1, 'B')
    ,(DATEADD(HOUR,-4,GETDATE() - 2), 123456, 1, 'B')
    ,(GETDATE(), 789012, 1, 'A')
    ,(DATEADD(HOUR,-2,GETDATE()), 789012, 1, 'A')
    ,(DATEADD(HOUR,-4,GETDATE()), 789012, 1, 'A')
    ,(GETDATE() - 1, 789012, 1, 'A')
    ,(DATEADD(HOUR,-2,GETDATE() - 1), 789012, 1, 'A')
    ,(DATEADD(HOUR,-4,GETDATE() - 1), 789012, 1, 'A')
    ,(GETDATE() - 2, 123456, 1, 'A')
    ,(DATEADD(HOUR,-2,GETDATE() - 2), 789012, 1, 'A')
    ,(DATEADD(HOUR,-4,GETDATE() - 2), 789012, 1, 'A')

Upvotes: 0

S3S
S3S

Reputation: 25112

Sounds like you want the distinct / unique count.

select count(*) from (
select 
    count(distinct cast(MeasureDate as date)) as CT,
    StudyNumber, 
    GroupNumber, 
    [Type]
from tUserEquipMeas
group by
    StudyNumber, 
    GroupNumber, 
    [Type])

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

count of date?

Select count(MeasureDate) AS MeasureDateCount,
StudyNumber, GroupNumber, [Type]
From tUserEquipMeas

Group by StudyNumber, GroupNumber, [type]

Upvotes: 0

Related Questions