Count distinct values - help needed.

Question

Count distinct values - help needed.

MickyD

SSCarpal Tunnel

Points: 4593
More actions
March 13, 2014 at 5:00 am

#289243

Hi everyone.
Can someone offers some advice on how to achieve this.
I need to count the number of records that exist for a distinct value in a table.
I.e. On a given date , how many records processed for distinct ID.
the table contains many many distinct ID's and I need to count them as number of records per ID on each day processed.
Does that make sense , please ask or advise on how to proceed ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

MickyD (3/13/2014)
Hi everyone.
Can someone offers some advice on how to achieve this.
I need to count the number of records that exist for a distinct value in a table.
I.e. On a given date , how many records processed for distinct ID.
the table contains many many distinct ID's and I need to count them as number of records per ID on each day processed.
Does that make sense , please ask or advise on how to proceed ?

SELECT processingday, ID, COUNT(*)

FROM mytable

GROUP BY processingday, ID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MickyD SSCarpal Tunnel Points: 4593 More actions · Answer 2

ChrisM@Work (3/13/2014)
MickyD (3/13/2014)
Hi everyone.
Can someone offers some advice on how to achieve this.
I need to count the number of records that exist for a distinct value in a table.
I.e. On a given date , how many records processed for distinct ID.
the table contains many many distinct ID's and I need to count them as number of records per ID on each day processed.
Does that make sense , please ask or advise on how to proceed ?
SELECT processingday, ID, COUNT(*)
FROM mytable
GROUP BY processingday, ID

Chris

Many thanks , thats great.

Only thing I need to resolve now is that I get multiple rows returned for ID all with 1 row returned as the date time group is different for each record returned.

example

Date

2014-02-06 13:15:56.180

ID

797

Rows returned

1

Date

2014-02-06 14:59:11.857

ID

797

Rows returned

1

etc , etc so I get 1 row for each diffrent date time group. Any way to create it so it returns a count for all ID based on whole Day ?

ie.

Date

2014-02-06

ID

797

Rows returned

1300

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Sure, try this:

SELECT processingday = CAST(processingday AS DATE), ID, COUNT(*)

FROM mytable

GROUP BY CAST(processingday AS DATE), ID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MickyD SSCarpal Tunnel Points: 4593 More actions · Answer 4

ChrisM@Work (3/13/2014)
Sure, try this:
SELECT processingday = CAST(processingday AS DATE), ID, COUNT(*)
FROM mytable
GROUP BY CAST(processingday AS DATE), ID

Chris

Perfect , thanks very much.

Will work out now what is going on.

Appreciate the assitance

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

You're welcome, good luck.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MickyD SSCarpal Tunnel Points: 4593 More actions · Answer 6

ChrisM@Work (3/13/2014)
You're welcome, good luck.

Chris

One last thing I hope you can help with.

My current query is :

SELECT EnqueueDate = CAST(EnqueueDate AS DATE), Siteid, COUNT(*) as Total

FROM MessageQueue

GROUP BY CAST(EnqueueDate AS DATE), Siteid

order by EnqueueDate desc

Which works fine , groups by day and total them

Last thing I need to sort is to list the SiteID name in the query results , that is held in another table called SiteID.

Tried various methods but don't seem to be getting it.

Could you possibly offer some advise ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

SELECT

EnqueueDate = CAST(m.EnqueueDate AS DATE),

m.Siteid,

s.[SiteID name],

Total = COUNT(*)

FROM MessageQueue m

INNER JOIN SiteID s

ON s.SiteID = m.SiteID

GROUP BY

CAST(m.EnqueueDate AS DATE),

m.Siteid,

s.[SiteID name]

ORDER BY m.EnqueueDate DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MickyD SSCarpal Tunnel Points: 4593 More actions · Answer 8

ChrisM@Work (3/13/2014)
SELECT EnqueueDate = CAST(m.EnqueueDate AS DATE), m.Siteid, s.[SiteID name], Total = COUNT(*) FROM MessageQueue m INNER JOIN SiteID s ON s.SiteID = m.SiteID GROUP BY CAST(m.EnqueueDate AS DATE), m.Siteid, s.[SiteID name] ORDER BY m.EnqueueDate DESC

Chris

Many thanks , perfect again.

regards

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Any time.

There are several ways to write this query, including joining the lookup to the original query as a derived table.

Thanks for the feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden