Sabz Sabz - 1 month ago 6
SQL Question

SQL Query - Get count of two columns from two tables

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?

FDL FDL
Answer

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

Comments