Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!v25g2000yqk.googlegroups.com!not-for-mail
From: Gints Plivna <gints.plivna@gmail.com>
Newsgroups: comp.databases.theory
Subject: Re: Making Count(*) return zero
Date: Wed, 9 Dec 2009 13:41:11 -0800 (PST)
Organization: http://groups.google.com
Lines: 16
Message-ID: <9e081b75-9f05-434c-8033-9d17568028a8@v25g2000yqk.googlegroups.com>
References: <45ecb476-76e7-4c56-940c-8298588d1617@j19g2000yqk.googlegroups.com>
NNTP-Posting-Host: 91.190.33.82
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1260394871 31785 127.0.0.1 (9 Dec 2009 21:41:11 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 9 Dec 2009 21:41:11 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: v25g2000yqk.googlegroups.com; posting-host=91.190.33.82; 
 posting-account=yUnXAgoAAAAtYWXOhuy56byPUjMGOID6
User-Agent: G2/1.0
X-HTTP-Via: 1.1 ISA
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) 
 AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.33 Safari/532.0,gzip(gfe),gzip(gfe)
Xref:  news.cambrium.nl

> if userid 1 has no records . it wont be returned in the query
> instead i want it to show zero so
> userid count
> 1 0
> 2 3
> 3 1
> 4 0

Scalar subqueries as already said or left join as follows:
select userid, count(sometable.val)
from users left join sometable
on (users.userid = sometable.val)
group by userid

Gints Plivna
http://www.gplivna.eu