CODEWITHSUNDEEP
sqljoin
repsajznav
repsajznav

Reputation: 61

SQL Count no result if condition is 0

Hi my code is right about showing the right result wherein count in another table with same value of column

My Database:

Clientgroup table

id | clientgroup_name | clientgroup_color
10   Business Customer  green
11   Super VIP          red
12   Ultimate VIP       pink

Client List Table:

id | name   | group_id 
1    John      10
2    Mark      10
3    Paul      12
4    Nico      12

My code:

SELECT Count(*) as Count,clientgroup_table.clientgroup_name,clientgroup_table.clientgroup_color FROM clientgroup_table, client_list WHERE client_list.group_id = clientgroup_table.id GROUP BY clientgroup_table.clientgroup_name

My result:

Count | clientgroup_name | clientgroup_color
2       Business Customer   green
2       Ultimate VIP        pink

My desired result:

Count | clientgroup_name | clientgroup_color
2       Business Customer   green
2       Ultimate VIP        pink
0       SUPER VIP           red

I want to show 0 even there's no row equal to it

Is it possible?

thanks

Upvotes: 0

Views: 69

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

Your current query has two problems which are giving you the wrong output. First, you are using an implicit inner join which is discarding records from the client group table which do not match. Instead, you should be using a LEFT JOIN. Second, you are counting with COUNT(*) which will give a false count of one for records on the left which do not match anything on the right. Instead, use COUNT(some column from the right).

SELECT
    COUNT(t2.group_id) AS Count,   -- returns 0 if t2.group_id is NULL
    t1.clientgroup_name,
    t1.clientgroup_color
FROM clientgroup_table t1
LEFT JOIN client_list t2
    ON t2.group_id = t1.id
GROUP BY
    t1.clientgroup_name,
    t1.clientgroup_color
ORDER BY
    Count DESC,
    t1.clientgroup_name;

Demo here:

Rextester

Upvotes: 1

Smart003
Smart003

Reputation: 1119

Try this one

if object_id('tempdb..#t1') is not null
drop table #t1
CREATE TABLE #t1
    ([id] int, [clientgroup_name] varchar(17), [clientgroup_color] varchar(5))
;

INSERT INTO #t1
    ([id], [clientgroup_name], [clientgroup_color])
VALUES
    (10, 'Business Customer', 'green'),
    (11, 'Super VIP', 'red'),
    (12, 'Ultimate VIP', 'pink')
;


if object_id('tempdb..#t2') is not null
drop table #t2
CREATE TABLE #t2
    ([id] int, [name] varchar(4), [group_id] int)
;

INSERT INTO #t2
    ([id], [name], [group_id])
VALUES
    (1, 'John', 10),
    (2, 'Mark', 10),
    (3, 'Paul', 12),
    (4, 'Nico', 12)
;

select count(r.group_id) count,t.clientgroup_name,t.clientgroup_color from #t1 t left join #t2 r on r.group_id=t.id
group by t.clientgroup_name,t.clientgroup_color

Upvotes: 0

user8193706
user8193706

Reputation: 2425

You can use left join in this case : 

SELECT COUNT(*) as Count, clientgroup_name,clientgroup_color FROM client_group 
cg LEFT JOIN client_list cl ON cg.id=cl.group_id GROUP BY 
cg.clientgroup_name,cg.clientgroup_color

Upvotes: 0

suguspnk
suguspnk

Reputation: 401

You can use the left join operation to do this to include the other rows that don't have any client. Try this query:

SELECT 
  Count(*) as Count,
  clientgroup_table.clientgroup_name,
  clientgroup_table.clientgroup_color 
FROM clientgroup_table left join client_list 
  on client_list.group_id = clientgroup_table.id 
GROUP BY clientgroup_table.clientgroup_name;

Upvotes: 0

Related Questions