CODEWITHSUNDEEP
sqlsql-serverjoin
mike100111
mike100111

Reputation: 960

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

Upvotes: 4

Views: 4986

Answers (1)

msheikh25
msheikh25

Reputation: 578

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

Upvotes: 3

Related Questions