SEO

#1 (**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

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

Merlin <ngroups@fastmail.fm> wrote in
<5rt2a7F16b504U1@mid.individual.net>:
> 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.
> >
> > 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
>
> Any ideas?

Yeah, a couple.

1). Fix your newsreader. You're missing the References
header.

2). Read 12.2.7.1 again, especially the part about the LEFT
JOINs. Note that with a LEFT JOIN, as the name suggests,
tables on the left and on the right of it are processed
differently.

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

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