CODEWITHSUNDEEP
sqldatabasedb2
SimpleGuy
SimpleGuy

Reputation: 2904

Complex SQL Query in JOIN

Tables

TRANSACTIONS

SUPP_ID   |  PAYMENT
----------+----------
  1001         200
  1002         100
  1005         250

MASTER_SUPPLIERS

SUPP_ID   |   AREA
----------+----------
  1001         ABC
  1002         XYZ
  1003         TYU
  1004         MNO
  1005         PQR

Intention: Find those count of suppliers area wise where no payment (NO_TRANS_CNT) has been received

SELECT AREA, COUNT(*) AS NO_TRANS_CNT FROM MASTER_SUPPLIERS
WHERE AREA NOT IN (SELECT DISTINCT(AREA) FROM TRANSACTIONS)
GROUP BY AREA

 AREA     | NO_TRANS_CNT
----------+--------------
  TYU           1
  MNO           1

Want to ask: Now, I also want to add the column TOTAL_SUPPLIERS in this area

  AREA     | TOTAL SUPPLIERS   |  NO_TRANS_CNT
----------+--------------------+----------------
  ABC              1                  0   
  XYZ              1                  0   
  TYU              1                  1                 
  MNO              1                  1
  PQR              1                  0

I think it can be achieved using JOINs, but I am not able to get how ?

Upvotes: 0

Views: 45

Answers (3)

Mark Barinstein
Mark Barinstein

Reputation: 12454

Try this:

SELECT 
  M.AREA
, COUNT(1) TOTAL_SUPPLIERS
, COUNT(CASE WHEN T.SUPP_ID IS NULL THEN 1 END) NO_TRANS_CNT
FROM MASTER_SUPPLIERS M
LEFT JOIN TRANSACTIONS T ON T.SUPP_ID = M.SUPP_ID
GROUP BY M.AREA;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

Use a left join, but start with suppliers:

select ms.area, count(*) as num_suppliers, count(t.supp_id) as num_transactions
from master_suppliers ms left join
     transactions t
     on t.supp_id = m.supp_id
group by ms.area;

The left join keeps everything in the first table -- which is what you want, along with matching rows from the second.

When you count the number of transactions, the argument to count() should either be a column used in the on clause or the primary key.

Upvotes: 0

Ronald
Ronald

Reputation: 2882

Something like

select M.AREA, COUNT(*) as TOTAL_SUPPLIERS, COUNT(T.PAYMENT) as NO_TRANS_CNT
from MASTER_SUPPLIERS M left join TRANSACTIONS T
     on M.SUPP_ID = T.SUPP_ID
group by M.AREA;

could work. Note that the COUNT(T.PAYMENT) only counts those where the PAYMENT is not NULL.

Upvotes: 0

Related Questions