Geek Answers Handbook

Aggregate count rows that have the same value in two columns in SQL

I am new to SQL and struggling with a slightly more complex query. I have a query that returns a sorted table as follows:

Updated request:

I mistakenly posted the wrong request. 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

I need to find out how many rows in the table has a corresponding meaning AideId, and EventName, and returns a number in a new column that will be shown to users. The result of the query is tied to WebDataGrid, and I will eventually be dealing with large amounts of data.

Among others, I found the following sources useful, but it seems it can't make it work. This question is about counting rows in a separate query: Count rows with identical values ​​in two columns

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

The following shows how to add a column: Add a column in an SQL query

SELECT 'Site1' AS SiteName,
        t1.column1, 
        t1.column2
FROM the-table

Is there a way to combine them to return a single table with the data I want? Something like this is possible:

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

Does that make 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 Information Table:

AidID   FirstName       LastName
12345   John            Doe
98765   Mike            Smith

Answer Im looking for:

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

The count column will be added based on the number of messages for the specific event that each emloyee has. If they have more than one, that means they answered. Otherwise, they did not.

Update # 2

, , , @M02:

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
+4
1

:

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

:

SELECT A,B,C,D,E,F, 
  count(a + b) over(partition by a,b) cnt
FROM the_table t

: http://rextester.com/KVDY97918

+3

More articles:


All Articles