mike100111 mike100111 - 2 years ago 71
SQL Question

Aggregate Count rows that have the same values in two columns in SQL

I am pretty new to SQL and am struggling with a slightly more advanced query. I have query which returns a sorted table like so:

Updated Query:

I mistakenly posted the wrong query. Here is the correct, similar query.

SELECT distinct T.AidId ,A.FirstName ,A.LastName ,T.EventName,
FROM AidsDB.dbo.AidsInfo A
JOIN AidsDB.dbo.TextsInfo T
ON A.AidId = T.AidId

What I need to do is find how many rows within the table have matching values of
and return the number in a new column to be shown to users. The query result is being bound to a
and I will eventually be dealing with large amounts of data.

Among others, I have found the following sources helpful, but cant seem to get it to work. This question addresses counting rows in a separate query: Count Rows with same values in two columns

FROM the-table

The following shows how to add a column: Add Column in SQL Query

SELECT 'Site1' AS SiteName,
FROM the-table

Is there any way to combine these to to return a single table with data I want? Something like this maybe:

SELECT (Count(*) Where EventName = EventName and AidId AidId) as Responses,
FROM [TextsInfo] ORDER BY [TextTime] DESC

Does this make any sense at all?

Any help is appreciated. Thanks in advance.

Update #1:

My data looks something like this:

Mesages Table:

AidID TextContents TextSender TextTime EventName
12345 Msg1 company1 2016-05-04 15:37:40.1522000 event1
12345 Reply to Msg1 John Doe 2016-05-04 15:38:29.0000000 event1
98765 Msg1 company1 2016-05-04 16:37:04.8458000 event1

Employee Info Table:

AidID FirstName LastName
12345 John Doe
98765 Mike Smith

Response Im looking for:

AidID FirstName LastName EventName Count
98765 Mike Smith Smith event1 1
12345 John Doe event1 2

The count column would be added based on how many messages for a specific Event each emloyee has. If they have more than one, it means they have responded. Otherwise they have not.

Update #2

For anyone interested, this is my working query based on @M02's answer:

select distinct T.AidId ,A.FirstName ,A.LastName ,T.EventName,
count(T.EventName + T.AidID) over(partition by T.EventName, T.AidID) cnt
from AidsDB.dbo.AidsInfo A join AidsDB.dbo.TextsInfo T on A.AidId = T.AidId

mo2 mo2
Answer Source

You can join the results from the group by query and get the results like this:

SELECT t.A,t.B,C,D,E,F,t1.cnt FROM the_table t
JOIN (SELECT A,B,COUNT(*) cnt FROM the_table GROUP BY A,B) t1
ON t.A = t1.A
AND t.B = t1.B

Here is another way to do it with an aggregate function:

  count(a + b) over(partition by a,b) cnt
FROM the_table t

You can see a demo of both of these methods here: http://rextester.com/KVDY97918

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download