CODEWITHSUNDEEP
sqloracle-database
user1639485
user1639485

Reputation: 808

Finding count and a field from two different tables

I have the following two tables:

Table A:
--------

Id     Service

101     S1
101     S1
101     S2
101     S2
102     S4

Table B:
--------
Service   Status
S1         Active
S1         Active
S2         Inactive
S2         Inactive
S4         Cancelled

Result Required:
----------------
Id    Service   Count Status
101   S1         2    Active
101   S2         2    Inactive
102   S4         1    Cancelled

How can we write one sql-query to get data from these two tables as per the result required?

Many thanks for your help!

Edit:

Is there a way where in we can populate different 'count-columns' of the table simultaneously using the same sql query. For instance, if the result is required in the form of:

Id    Service   Active_Count Inactive_Count Cancelled_Count
---------------------------------------------------------------------
   101     S1          2            0              2
   101     S1          0            2              0
   102     S4          0            0              1

Upvotes: 0

Views: 55

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172608

try this:

select id, A.service, count(1), status 
from A inner join B on A.service = B.service 
group by id, A.service, status

Upvotes: 0

david a.
david a.

Reputation: 5291

select id, A.service, count(1), status 
from A, B 
where A.service = B.service 
group by id, A.service, status

Upvotes: 2

Related Questions