SQL Query - Get count of two columns from two tables

sql, sql-server, sql-server-2008

Solution

A simpler solution from a "statement point of view" (without `COALESCE` that maybe it's not so easy to understand).

Pay attention to the performances:

Select T1.TicketNumber,T1.Rules_Count_1,T2.Rules_Count_2
FROM
(
  Select [TicketNumber], COUNT([TicketNumber]) AS Rules_Count_1 
  from [Table1] T1
  group by TicketNumber) T1
INNER JOIN
(
  Select [TicketNumber], COUNT([TicketNumber]) AS Rules_Count_2
  from [Table2] T2
  group by TicketNumber
 ) T2
on T1.TicketNumber = T2.TicketNumber

SQL Fiddle Demo

Problem

Table 1: ``` TicketNumber | Rules --------------------------- PR123 | rule_123 PR123 | rule_234 PR123 | rule_456 PR999 | rule_abc PR999 | rule_xyz ``` Table2: ``` TicketNumber | Rules --------------------------- PR123 | rule_123 PR123 | rule_234 PR999 | rule_abc ``` NOTE: Both tables have the same structure: same column names but different count. NOTE: Both tables have same set of TicketNumber values CASE 1: If I need ticket and rules count of each ticket from table1, the query is: ``` Select [TicketNo], COUNT([TicketNo]) AS Rules_Count from [Table1] group by TicketNo ``` This will give me output in format : ``` ticketNumber | Rules_Count --------------------------- PR123 | 3 PR999 | 9 ``` CASE 2: (NEED HELP WITH THIS) Now, the previous query gets the ticket and the count of the ticket of only 1 table. I need the count of the same ticket (since both have same set of tkt nos) in table2 also. I need result in this way: ``` ticketNumber | Count(ticketNumber) of table1 | Count(ticketNumber) of table2 --------------------------------------------------------------------------------- PR123 | 3 | 2 PR999 | 2 | 1 ``` Both Table1 and table2 have the same set of ticket nos but different counts How do i get the result as shown above?

Original source