CODEWITHSUNDEEP
sql
user2590547
user2590547

Reputation: 3

add zero to the count where ever there no data for field

I have two tables TAB1 and TAB2

TAB1 :

ID  NAME
1   Supreeth
2   Aishu
3   Arvi
4   Gani

TAB2 :

ID  DESIGN
1   A
1   B
4   B
4   C
2   A
3   B
3   B

I'm trying to get the count of DESIGN, I'm getting the following output 

NAME        DESIGN  Count(DESIGN)
Supreeth        A        1
Supreeth        B        1
Aishu           A        1
Arvi            B        2
Gani            B        1
Gani            C        1

The query I'm trying is

SELECT TAB1.NAME,TAB2.DESIGN, COUNT(DESIGN) 
FROM TAB1 , TAB2
WHERE TAB1.ID=TAB2.ID
GROUP BY TAB1.NAME,TAB2.DESIGN,TAB1.ID
ORDER BY TAB1.ID

But I'm looking for the output like


NAME        DESIGN  Count(DESIGN)
Supreeth        A        1
Supreeth        B        1
Supreeth        C        0
Aishu           A        1
Aishu           B        0
Aishu           C        0
Arvi            A        0
Arvi            B        2
Arvi            C        0
Gani            A        0
Gani            B        1
Gani            C        1

Please tell me how to achieve this using SQL query. Thanks in advance

Upvotes: 0

Views: 90

Answers (2)

bvr
bvr

Reputation: 4826

Try this

SELECT T1.[ID], T1.[NAME],T2.[DESIGN],
(
  SELECT COUNT([DESIGN]) FROM TAB2 TT2 WHERE TT2.ID=T1.ID AND TT2.[DESIGN] = T2.[DESIGN]
) AS CNT
FROM TAB1 T1
CROSS JOIN 
(
  SELECT [DESIGN] FROM TAB2 GROUP BY [DESIGN]
) T2
ORDER BY T1.[NAME],T2.[DESIGN]

SQL FIDDLE DEMO

Upvotes: 0

Daniel Neal
Daniel Neal

Reputation: 4173

Try this:

SELECT 
   TAB1.ID,
   TAB1.NAME,TAB2.DESIGN,
   SUM(CASE WHEN TAB1.ID=TAB2.ID THEN 1 ELSE 0 END) as count
FROM 
   TAB1 CROSS JOIN TAB2
GROUP BY 
   TAB1.ID,TAB1.NAME,TAB2.DESIGN
ORDER BY 
   TAB1.ID

SQLFiddle

Upvotes: 3

Related Questions