StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTSQL DISTINCT RECORD COUNT
text
Body
copied!<hr> <p>I am using SSMS 2008 and am trying to get a distinct count of Event_names from my table. But instead, it is returning count = 1 when there are actually 2 records and other invalid values. What I want is the total count / consumer for their number of distinct event_names they are part of. Also, some of the Event Counts for the same consumer are different. But I want one distinct count / consumer. Here is a portion of the data returned by my invalid query:</p> <h2>consumer Program Enrollment Date event_name Event_Ct</h2> <pre><code>B, Tiffany 2010-09-27 12:00:00.000 Comprehensive Clinical Assessment 1 B, Tiffany 2010-09-27 00:00:00.000 Telemedicine Comprehensive Clinical Assessment 1 B, Nickolas Tyan 2010-12-07 15:00:00.000 Comprehensive Clinical Assessment 1 B, Nickolas Tyan 2010-12-07 00:00:00.000 Telemedicine Comprehensive Clinical Assessment 1 B, Jack 2011-06-13 08:30:00.000 Comprehensive Clinical Assessment 1 B, Jack 2011-01-03 00:00:00.000 Medication Management 1 B, Victoria Lynn 2010-11-10 00:00:00.000 Telemedicine Comprehensive Clinical Assessment 3 B, Victoria Lynn 2010-12-28 00:00:00.000 Telemedicine Psychiatric Assessment 3 B, Victoria Lynn 2011-01-07 00:00:00.000 Telemedicine Psychiatric Progress Note 2 B, Victoria Lynn 2011-02-08 00:00:00.000 Telemedicine Psychiatric Progress Note 2 </code></pre> <p>And here is the T-SQL I used for the above:</p> <pre><code>SELECT consumer, [Program Enrollment Date], event_name, [Program Quarter] INTO #INITIATIONS FROM #consumer_initiations WHERE consumer IN (SELECT DISTINCT Consumer FROM #consumer_initiations GROUP BY Consumer HAVING Count(DISTINCT event_name) > 1) ORDER BY consumer, event_name SELECT A.consumer, A.[Program Enrollment Date], A.event_name, count(A.event_name) FROM #INITIATIONS A JOIN #INITIATIONS B ON A.consumer = B.consumer AND A.event_name <> B.event_name AND A.[Program Enrollment Date] <> B.[Program Enrollment Date] GROUP BY A.consumer, A.event_name, a.[Program Enrollment Date] </code></pre> <p>I also tried this query, which returned correct counts for consumers with only 2 records, but for those with > 2 records, it returned too high of counts:</p> <pre><code>SELECT A.consumer, count(A.event_name) FROM #INITIATIONS A JOIN #INITIATIONS B ON A.consumer = B.consumer AND A.event_name <> B.event_name AND A.[Program Enrollment Date] <> B.[Program Enrollment Date] GROUP BY A.consumer </code></pre> <p>Thanks to Chris, here is the solution that worked:</p> <pre><code>Select I.consumer, [Program Enrollment Date], event_name, countPerConsumer.[Number of Events] From #INITIATIONS i Join (Select Consumer, count(distinct event_name) as [Number of Events] From #INITIATIONS GROUP BY Consumer) countPerConsumer on countPerConsumer.Consumer </code></pre> <p>= i.consumer </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload