mike100111 mike100111 - 7 months ago 15
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
AideId
and
EventName
and return the number in a new column to be shown to users. The query result is being bound to a
WebDataGrid
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

SELECT A,
B,
COUNT(*)
FROM the-table
GROUP BY A,B


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

SELECT 'Site1' AS SiteName,
t1.column1,
t1.column2
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,
[TextTime],
[TextSender],
[TextContents],
[AidID],
[EventName]
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

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:

SELECT A,B,C,D,E,F, 
  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