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

Question

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

Please, add samples of data you have and what output you want. — gofr1
– gofr1, Commented May 7, 2016 at 11:41

msheikh25 · Accepted Answer · 2016-05-07 00:27:13Z

3

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

edited May 7, 2016 at 0:27

answered May 7, 2016 at 0:09

msheikh25

5783 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mike100111 Over a year ago

Thanks!! This worked perfectly. I am using method two currently. My query has a join method(see updated post), is there a performance difference between using a nested query and an aggregate method in my case? Thanks.

Collectives™ on Stack Overflow

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

Updated Query:

Update #1:

Mesages Table:

Employee Info Table:

Response Im looking for:

Update #2

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Updated Query:

Update #1:

Mesages Table:

Employee Info Table:

Response Im looking for:

Update #2

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related