SEO

#1 (**permalink**) 02-28-2008, 10:30 AM

Hi there,

I am having trouble with count/sum. The Id's that have 0 results
within the count statement do not return with the result. I would need
to have that rows listed as well.

e.g.

Now:
1 = 400
3 = 192

Should be:
1 = 400
2 = 0
3 = 192

Here is my statement. I am using mysql 4.1:

SELECT
count(*) AS ct,
group_name,
group_id
FROM
user u,
age_description ad
WHERE
points >= 5000
AND
ad.group_id = u.age
GROUP BY
ad.group_id
ORDER BY
group_id

However I try it I can't get the rows with 0 value.

Thank you for any help on that!

Merlin

#2 (**permalink**) 02-28-2008, 10:30 AM

Merlin <ngroups@fastmail.fm> wrote in
<5rsm8pF16aufeU1@mid.individual.net>:
> Now:
> 1 = 400
> 3 = 192
>
> Should be:
> 1 = 400
> 2 = 0
> 3 = 192

You didn't provide an actual sample dataset and resulting
rows, but at a guess, you'll need a LEFT JOIN. Read about
LEFT JOINs in MySQL Reference Manual.

> SELECT
> count(*) AS ct,
> group_name,
> group_id
> FROM
> user u,
> age_description ad
> WHERE
> points >= 5000
> AND
> ad.group_id = u.age
> GROUP BY
> ad.group_id
> ORDER BY
> group_id

Note that you'll need to modify your aggregate expression as
well, since what you need to count are users, not rows.

--
....also, I submit that we all must honourably commit seppuku
right now rather than serve the Dark Side by producing the
HTML 5 spec.

#3 (**permalink**) 02-28-2008, 10:30 AM

Pavel Lepin schrieb:
> Merlin <ngroups@fastmail.fm> wrote in
> <5rsm8pF16aufeU1@mid.individual.net>:
>> Now:
>> 1 = 400
>> 3 = 192
>>
>> Should be:
>> 1 = 400
>> 2 = 0
>> 3 = 192
>
> You didn't provide an actual sample dataset and resulting
> rows, but at a guess, you'll need a LEFT JOIN. Read about
> LEFT JOINs in MySQL Reference Manual.
>
>> SELECT
>> count(*) AS ct,
>> group_name,
>> group_id
>> FROM
>> user u,
>> age_description ad
>> WHERE
>> points >= 5000
>> AND
>> ad.group_id = u.age
>> GROUP BY
>> ad.group_id
>> ORDER BY
>> group_id
>
> Note that you'll need to modify your aggregate expression as
> well, since what you need to count are users, not rows.
>

I did also try it with the left join but same result:

SELECT count(ad.group_id) AS ct, group_name, group_id FROM user u
LEFT JOIN user_agegroups_desc ad on ad.group_id = u.age WHERE points >=
5000 GROUP BY ad.group_id ORDER BY group_id

Table age_groups:
group_id group_name
1 under 15
2 16-20
3 21-25
4 26-30
5 31-35
6 36-40
7 41-45
8 46-50
9 51-55
10 56-60
11 61-65
12 Over 65

Any ideas?

Thank you for any help,

Merlin

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

COUNT() - returning rows with 0 value