SQL Query - Get count of two columns from two tables

Question

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?

I removed the MySQL tag because the syntax suggests SQL Server. — Gordon Linoff
– Gordon Linoff, Commented Aug 27, 2013 at 13:23

Francesco De Lisi · Accepted Answer · 2013-08-27 13:34:50Z

8

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

answered Aug 27, 2013 at 13:34

Francesco De Lisi

1,5239 silver badges20 bronze badges

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

4 Comments

Sabz Over a year ago

HOwever, InnerJoin means that the count(table1) should be > than count(table2) right? What if its different?

Francesco De Lisi Over a year ago

Inner join means "keep only the rows present in both the tables and join them". Thus, if you put a row with TicketNumberX in Table1 and 42 rows with TicketNumberX in Table2 it should work the same way. But if you put 0 rows with TicketNumberX in Table2 you'll never get a join.

Sabz Over a year ago

Thanks. Is there anyway to do this without using JOINS?

Francesco De Lisi Over a year ago

Yes but it's not a good idea due to intensive filtering by WHERE clause and UNION in place of a simple JOIN.

Gordon Linoff · Accepted Answer · 2013-08-27 13:26:12Z

2

You can do this with a full outer join after aggregation (or an inner join if you really know that both tables have the same tickets:

select coalesce(t1.TicketNo, t2.TicketNo) as TicketNo,
       coalesce(t1.Rules_Count, 0) as t1_Rules_Count,
       coalesce(t2.Rules_Count, 0) as t2_Rules_Count
from (Select [TicketNo], COUNT([TicketNo]) AS Rules_Count
      from [Table1]
      group by TicketNo
     ) t1 full outer join
     (Select [TicketNo], COUNT([TicketNo]) AS Rules_Count
      from [Table2]
      group by TicketNo
     ) t2
     on t1.TicketNo = t2.TicketNo;

answered Aug 27, 2013 at 13:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Sabz Over a year ago

Thanks Gordon, Your query works perfectly.However, outer join means that table2 should contain more count than table1 right? What to do if the counts vary?

Gordon Linoff Over a year ago

@sabz . . . A full outer join returns all the rows from both tables. You may be thinking of a left outer join or right outer join.

TT. · Accepted Answer · 2016-10-30 06:15:51Z

0

SELECT A.center,
       A.total_1st,
       B.total_2nd
FROM   (SELECT a.center,
               Count (a.dose1) AS Total_1st
        FROM   table_1 a
        GROUP  BY a.center) A
       INNER JOIN (SELECT b.center,
                          Count (b.dose2) AS Total_2nd
                   FROM   table_2 b
                   GROUP  BY b.center) B
               ON a.center = b.center
ORDER  BY A.center

edited Oct 30, 2016 at 6:15

TT.

16.2k6 gold badges53 silver badges95 bronze badges

answered Oct 30, 2016 at 6:03

Almamun

113 bronze badges

Collectives™ on Stack Overflow

SQL Query - Get count of two columns from two tables

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related