Mysql count(condition)?

[dottty]

Hi,
i have a table that has the following fields:
id, name, dept, pay
1, John, Sales, 4000
2, Peter, HR, 5000
etc.

How do i count how many people there are in each dept with an sql query?

I find this surprisingly difficult, given the fact that i can sum the total
pay for each dept by using:
$query="
Select sum(if (dept='HR', pay, 0)) as hrpay, ......
";

TIA

[Bosconian]

Re: Mysql count(condition )?

"dottty" <dottty@noemail pls.met> wrote in message
news:4406695a$0 $15795$14726298 @news.sunsite.d k...[color=blue]
> Hi,
> i have a table that has the following fields:
> id, name, dept, pay
> 1, John, Sales, 4000
> 2, Peter, HR, 5000
> etc.
>
> How do i count how many people there are in each dept with an sql query?
>
> I find this surprisingly difficult, given the fact that i can sum the[/color]
total[color=blue]
> pay for each dept by using:
> $query="
> Select sum(if (dept='HR', pay, 0)) as hrpay, ......
> ";
>
> TIA
>
>
>[/color]

SELECT Count(dept) AS deptcount
FROM [table]
GROUP BY id

[Steve]

Re: Mysql count(condition )?

On Thu, 02 Mar 2006 11:41:12 +0800, dottty wrote:
[color=blue]
> Hi,
> i have a table that has the following fields:
> id, name, dept, pay
> 1, John, Sales, 4000
> 2, Peter, HR, 5000
> etc.
>
> How do i count how many people there are in each dept with an sql query?
>
> I find this surprisingly difficult, given the fact that i can sum the total
> pay for each dept by using:
> $query="
> Select sum(if (dept='HR', pay, 0)) as hrpay, ......
> ";
>
> TIA[/color]

Blimey, is that sql? Given the standard rider about this *not* being a
mysql support group, something like the (untested) suggestion below might
help:

select dept, count(*), sum(pay) from ... group by dept, order by dept;

Steve

[no.mail.pls]

Re: Mysql count(condition )?

Hi,

Thanks for the prompt reply. However, i think it should be GROUP BY dept.

cheers

"Bosconian" <bosconian@plan etx.com> wrote in message
news:_pqdnfMwK-jE95vZnZ2dnUVZ_ v6dnZ2d@comcast .com...[color=blue]
> "dottty" <dottty@noemail pls.met> wrote in message
> news:4406695a$0 $15795$14726298 @news.sunsite.d k...[color=green]
>> Hi,
>> i have a table that has the following fields:
>> id, name, dept, pay
>> 1, John, Sales, 4000
>> 2, Peter, HR, 5000
>> etc.
>>
>> How do i count how many people there are in each dept with an sql query?
>>
>> I find this surprisingly difficult, given the fact that i can sum the[/color]
> total[color=green]
>> pay for each dept by using:
>> $query="
>> Select sum(if (dept='HR', pay, 0)) as hrpay, ......
>> ";
>>
>> TIA
>>
>>
>>[/color]
>
> SELECT Count(dept) AS deptcount
> FROM [table]
> GROUP BY id
>
>[/color]

[no.mail.pls]

Re: Mysql count(condition )?

Hi Steve,

Thanks for the reply. It works.
i am aware that this is not a sql group, but i beg your indulgence, as i am
quite sure that you guys will respond,
because i believe that most folks who use PHP
will also be using mysql.

is that correct?

cheers
"Steve" <ThisOne@Aint.V alid> wrote in message
news:pan.2006.0 3.02.04.04.02.8 10758@Aint.Vali d...[color=blue]
> On Thu, 02 Mar 2006 11:41:12 +0800, dottty wrote:
>[color=green]
>> Hi,
>> i have a table that has the following fields:
>> id, name, dept, pay
>> 1, John, Sales, 4000
>> 2, Peter, HR, 5000
>> etc.
>>
>> How do i count how many people there are in each dept with an sql query?
>>
>> I find this surprisingly difficult, given the fact that i can sum the
>> total
>> pay for each dept by using:
>> $query="
>> Select sum(if (dept='HR', pay, 0)) as hrpay, ......
>> ";
>>
>> TIA[/color]
>
> Blimey, is that sql? Given the standard rider about this *not* being a
> mysql support group, something like the (untested) suggestion below might
> help:
>
> select dept, count(*), sum(pay) from ... group by dept, order by dept;
>
> Steve
>[/color]

[Jerry Stuckle]

Re: Mysql count(condition )?

no.mail.pls wrote:[color=blue]
> Hi Steve,
>
> Thanks for the reply. It works.
> i am aware that this is not a sql group, but i beg your indulgence, as i am
> quite sure that you guys will respond,
> because i believe that most folks who use PHP
> will also be using mysql.
>
> is that correct?
>
> cheers
> "Steve" <ThisOne@Aint.V alid> wrote in message
> news:pan.2006.0 3.02.04.04.02.8 10758@Aint.Vali d...
>[color=green]
>>On Thu, 02 Mar 2006 11:41:12 +0800, dottty wrote:
>>
>>[color=darkred]
>>>Hi,
>>>i have a table that has the following fields:
>>>id, name, dept, pay
>>>1, John, Sales, 4000
>>>2, Peter, HR, 5000
>>>etc.
>>>
>>>How do i count how many people there are in each dept with an sql query?
>>>
>>>I find this surprisingly difficult, given the fact that i can sum the
>>>total
>>>pay for each dept by using:
>>>$query="
>>>Select sum(if (dept='HR', pay, 0)) as hrpay, ......
>>>";
>>>
>>>TIA[/color]
>>
>>Blimey, is that sql? Given the standard rider about this *not* being a
>>mysql support group, something like the (untested) suggestion below might
>>help:
>>
>>select dept, count(*), sum(pay) from ... group by dept, order by dept;
>>
>>Steve
>>[/color]
>
>
>[/color]

No, it is not correct. Many use Postgres SQL, SQL Server, other
databases or no database at all.

You should ask MySQL question in a MySQL newsgroup - such as
comp.databases. mysql. *Everyone* there uses MySQL.


--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Bosconian]

Re: Mysql count(condition )?

"no.mail.pl s" <no.mail@st.pet ers> wrote in message
news:44066ee9$0 $15795$14726298 @news.sunsite.d k...[color=blue]
> Hi,
>
> Thanks for the prompt reply. However, i think it should be GROUP BY dept.
>
> cheers
>
> "Bosconian" <bosconian@plan etx.com> wrote in message
> news:_pqdnfMwK-jE95vZnZ2dnUVZ_ v6dnZ2d@comcast .com...[color=green]
> > "dottty" <dottty@noemail pls.met> wrote in message
> > news:4406695a$0 $15795$14726298 @news.sunsite.d k...[color=darkred]
> >> Hi,
> >> i have a table that has the following fields:
> >> id, name, dept, pay
> >> 1, John, Sales, 4000
> >> 2, Peter, HR, 5000
> >> etc.
> >>
> >> How do i count how many people there are in each dept with an sql[/color][/color][/color]
query?[color=blue][color=green][color=darkred]
> >>
> >> I find this surprisingly difficult, given the fact that i can sum the[/color]
> > total[color=darkred]
> >> pay for each dept by using:
> >> $query="
> >> Select sum(if (dept='HR', pay, 0)) as hrpay, ......
> >> ";
> >>
> >> TIA
> >>
> >>
> >>[/color]
> >
> > SELECT Count(dept) AS deptcount
> > FROM [table]
> > GROUP BY id
> >
> >[/color]
>
>[/color]

You're right and I know better. Serves me right for responding hastily just
as the pizza's being delivered. :-]

[Bosconian]

Re: Mysql count(condition )?

"no.mail.pl s" <no.mail@st.pet ers> wrote in message
news:44067338$0 $15789$14726298 @news.sunsite.d k...[color=blue]
> Hi Steve,
>
> Thanks for the reply. It works.
> i am aware that this is not a sql group, but i beg your indulgence, as i[/color]
am[color=blue]
> quite sure that you guys will respond,
> because i believe that most folks who use PHP
> will also be using mysql.
>
> is that correct?
>
> cheers
> "Steve" <ThisOne@Aint.V alid> wrote in message
> news:pan.2006.0 3.02.04.04.02.8 10758@Aint.Vali d...[color=green]
> > On Thu, 02 Mar 2006 11:41:12 +0800, dottty wrote:
> >[color=darkred]
> >> Hi,
> >> i have a table that has the following fields:
> >> id, name, dept, pay
> >> 1, John, Sales, 4000
> >> 2, Peter, HR, 5000
> >> etc.
> >>
> >> How do i count how many people there are in each dept with an sql[/color][/color][/color]
query?[color=blue][color=green][color=darkred]
> >>
> >> I find this surprisingly difficult, given the fact that i can sum the
> >> total
> >> pay for each dept by using:
> >> $query="
> >> Select sum(if (dept='HR', pay, 0)) as hrpay, ......
> >> ";
> >>
> >> TIA[/color]
> >
> > Blimey, is that sql? Given the standard rider about this *not* being a
> > mysql support group, something like the (untested) suggestion below[/color][/color]
might[color=blue][color=green]
> > help:
> >
> > select dept, count(*), sum(pay) from ... group by dept, order by dept;
> >
> > Steve
> >[/color]
>
>[/color]

I'm with you. PHP and MySQL are virtually joined at the hip. Look no further
than www.opensourcecms.com for proof.

[Geoff Muldoon]

Re: Mysql count(condition )?

bosconian@plane tx.com says...
[color=blue][color=green]
> > because i believe that most folks who use PHP
> > will also be using mysql.[/color]
>
> I'm with you. PHP and MySQL are virtually joined at the hip. Look no further
> than www.opensourcecms.com for proof.[/color]

And yet here I sit churning away on PHP/AdoDB/Oracle ...

comp.databases. mysql is far better for straight MySQL advice.

Geoff M

[no.mail.pls]

Re: Mysql count(condition )?

Hiya,

Thanks for all the responses. i have never used mysql usenet groups, so i
just popped in to take a look, and found that they are at least an order
less popular than the php groups. On my ISP, i found the following number of
posts for these groups:

comp.lang.php - 22296 posts
comp.databases. mysql - 1067 posts
alt.comp.databa ses.mysql - 128 posts

i know it is a poor excuse for posting here though, but it is kind of you
folks to reply.

The huge difference in the number of posts sets me wondering... is it much
easier to use SQL than PHP, and so there is nothing much to discuss? Or are
there some other reasons?


cheers

"Geoff Muldoon" <geoff.muldoon@ trap.gmail.com> wrote in message
news:MPG.1e7138 c53cab4fab98977 5@news.readfree news.net...[color=blue]
> bosconian@plane tx.com says...
>[color=green][color=darkred]
>> > because i believe that most folks who use PHP
>> > will also be using mysql.[/color]
>>
>> I'm with you. PHP and MySQL are virtually joined at the hip. Look no
>> further
>> than www.opensourcecms.com for proof.[/color]
>
> And yet here I sit churning away on PHP/AdoDB/Oracle ...
>
> comp.databases. mysql is far better for straight MySQL advice.
>
> Geoff M[/color]

[Steve]

Re: Mysql count(condition )?

On Thu, 02 Mar 2006 14:38:20 +0800, no.mail.pls wrote:
[color=blue]
> Hiya,
>
> Thanks for all the responses. i have never used mysql usenet groups, so i
> just popped in to take a look, and found that they are at least an order
> less popular than the php groups. On my ISP, i found the following number of
> posts for these groups:
>
> comp.lang.php - 22296 posts
> comp.databases. mysql - 1067 posts
> alt.comp.databa ses.mysql - 128 posts
>
> i know it is a poor excuse for posting here though, but it is kind of you
> folks to reply.
>
> The huge difference in the number of posts sets me wondering... is it much
> easier to use SQL than PHP, and so there is nothing much to discuss? Or are
> there some other reasons?
>
>[/color]
Could it be that it's a new group?

[Jerry Stuckle]

Re: Mysql count(condition )?

no.mail.pls wrote:[color=blue]
> Hiya,
>
> Thanks for all the responses. i have never used mysql usenet groups, so i
> just popped in to take a look, and found that they are at least an order
> less popular than the php groups. On my ISP, i found the following number of
> posts for these groups:
>
> comp.lang.php - 22296 posts
> comp.databases. mysql - 1067 posts
> alt.comp.databa ses.mysql - 128 posts
>
> i know it is a poor excuse for posting here though, but it is kind of you
> folks to reply.
>
> The huge difference in the number of posts sets me wondering... is it much
> easier to use SQL than PHP, and so there is nothing much to discuss? Or are
> there some other reasons?
>
>
> cheers
>
> "Geoff Muldoon" <geoff.muldoon@ trap.gmail.com> wrote in message
> news:MPG.1e7138 c53cab4fab98977 5@news.readfree news.net...
>[color=green]
>>bosconian@pla netx.com says...
>>
>>[color=darkred]
>>>>because i believe that most folks who use PHP
>>>>will also be using mysql.
>>>
>>>I'm with you. PHP and MySQL are virtually joined at the hip. Look no
>>>further
>>>than www.opensourcecms.com for proof.[/color]
>>
>>And yet here I sit churning away on PHP/AdoDB/Oracle ...
>>
>>comp.database s.mysql is far better for straight MySQL advice.
>>
>>Geoff M[/color]
>
>
>[/color]

Number of posts doesn't mean anything. I can point you to some usenet
groups which have a lot of posts, but aren't worth visiting because so
few of those posts are on topic.

In the case of comp.database.m ysql, it's a relatively new newsgroup so
it doesn't have the history this one does. And every post on there is
about MySQL.

Most posts here are about PHP.

Asking MySQL questions in a PHP group is like getting brain surgery at
McDonalds because "more people go there".

Ask in the right group and you'll get better answers.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: Mysql count(condition )?

Bosconian wrote:[color=blue]
> "no.mail.pl s" <no.mail@st.pet ers> wrote in message
> news:44067338$0 $15789$14726298 @news.sunsite.d k...
>[color=green]
>>Hi Steve,
>>
>>Thanks for the reply. It works.
>>i am aware that this is not a sql group, but i beg your indulgence, as i[/color]
>
> am
>[color=green]
>>quite sure that you guys will respond,
>>because i believe that most folks who use PHP
>>will also be using mysql.
>>
>>is that correct?
>>
>>cheers
>>"Steve" <ThisOne@Aint.V alid> wrote in message
>>news:pan.2006 .03.02.04.04.02 .810758@Aint.Va lid...
>>[color=darkred]
>>>On Thu, 02 Mar 2006 11:41:12 +0800, dottty wrote:
>>>
>>>
>>>>Hi,
>>>>i have a table that has the following fields:
>>>>id, name, dept, pay
>>>>1, John, Sales, 4000
>>>>2, Peter, HR, 5000
>>>>etc.
>>>>
>>>>How do i count how many people there are in each dept with an sql[/color][/color]
>
> query?
>[color=green][color=darkred]
>>>>I find this surprisingly difficult, given the fact that i can sum the
>>>>total
>>>>pay for each dept by using:
>>>>$query="
>>>>Select sum(if (dept='HR', pay, 0)) as hrpay, ......
>>>>";
>>>>
>>>>TIA
>>>
>>>Blimey, is that sql? Given the standard rider about this *not* being a
>>>mysql support group, something like the (untested) suggestion below[/color][/color]
>
> might
>[color=green][color=darkred]
>>>help:
>>>
>>>select dept, count(*), sum(pay) from ... group by dept, order by dept;
>>>
>>>Steve
>>>[/color]
>>
>>[/color]
>
> I'm with you. PHP and MySQL are virtually joined at the hip. Look no further
> than www.opensourcecms.com for proof.
>
>[/color]

So? You have a cms which uses MySQL. I can point you to other products
which use Postgres. Does that mean PHP and Postgres are "virtually
joined at the hip"? Or maybe Oracle? Or MSSQL? There are other
products which use each of these with PHP.

PHP is a programming language. MySQL is a database manager. Two
entirely different products which work well together.

And two entirely different newsgroups.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Erwin Moller]

Re: Mysql count(condition )?

no.mail.pls wrote:
[color=blue]
> Hi Steve,
>
> Thanks for the reply. It works.
> i am aware that this is not a sql group, but i beg your indulgence, as i
> am quite sure that you guys will respond,
> because i believe that most folks who use PHP
> will also be using mysql.
>
> is that correct?
>[/color]

Personally I prefer Postgresql: a lot more mature than mySQL, but mySQL is
catching up very quickly lately. :-)

Problem with Postgresql was it only ran on *nix, while mySQL did M$ too.
(Now Postgresql is ported to W32, but I have no idea how stable it runs on
W$ machines)

So a lot of of W$-only people were confortable with mysql and not with
Postgresql, and as we all know: the W$ people come in massive numbers.

I always felt a bit sorry for Postgresql for that, it deserved to be the
most popular database in my opinion, not the second or third in line.
(Nothing against mySQL).

Regards,
Erwin Moller

[no.mail.pls]

Re: Mysql count(condition )?

Hi,

I've not used Postgresql for the reason that Erwin mentioned. Would like to
give it a try.

cheers

"Erwin Moller"
<since_humans_r ead_this_I_am_s pammed_too_much @spamyourself.c om> wrote in
message news:4406f7a9$0 $11063$e4fe514c @news.xs4all.nl ...[color=blue]
> no.mail.pls wrote:
>[color=green]
>> Hi Steve,
>>
>> Thanks for the reply. It works.
>> i am aware that this is not a sql group, but i beg your indulgence, as i
>> am quite sure that you guys will respond,
>> because i believe that most folks who use PHP
>> will also be using mysql.
>>
>> is that correct?
>>[/color]
>
> Personally I prefer Postgresql: a lot more mature than mySQL, but mySQL is
> catching up very quickly lately. :-)
>
> Problem with Postgresql was it only ran on *nix, while mySQL did M$ too.
> (Now Postgresql is ported to W32, but I have no idea how stable it runs on
> W$ machines)
>
> So a lot of of W$-only people were confortable with mysql and not with
> Postgresql, and as we all know: the W$ people come in massive numbers.
>
> I always felt a bit sorry for Postgresql for that, it deserved to be the
> most popular database in my opinion, not the second or third in line.
> (Nothing against mySQL).
>
> Regards,
> Erwin Moller[/color]