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]

"dottty" <do****@noemailpls.met> wrote in message
news:44***********************@news.sunsite.dk...

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

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

[Steve]

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 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

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]

Hi,

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

cheers

"Bosconian" <bo*******@planetx.com> wrote in message
news:_p******************************@comcast.com. ..

"dottty" <do****@noemailpls.met> wrote in message
news:44***********************@news.sunsite.dk...

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

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

[no.mail.pls]

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" <Th*****@Aint.Valid> wrote in message
news:pa****************************@Aint.Valid...

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 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

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

[Jerry Stuckle]

no.mail.pls wrote:

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" <Th*****@Aint.Valid> wrote in message
news:pa****************************@Aint.Valid...

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 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

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, 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.
js*******@attglobal.net
==================

[Bosconian]

"no.mail.pls" <no*****@st.peters> wrote in message
news:44***********************@news.sunsite.dk...

Hi,

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

cheers

"Bosconian" <bo*******@planetx.com> wrote in message
news:_p******************************@comcast.com. ..

"dottty" <do****@noemailpls.met> wrote in message
news:44***********************@news.sunsite.dk...

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

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

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

[Bosconian]

"no.mail.pls" <no*****@st.peters> wrote in message
news:44***********************@news.sunsite.dk...

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" <Th*****@Aint.Valid> wrote in message
news:pa****************************@Aint.Valid...

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 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

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

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

[Geoff Muldoon]

bo*******@planetx.com says...

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.

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]

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.databases.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" <ge***********@trap.gmail.com> wrote in message
news:MP************************@news.readfreenews. net...

bo*******@planetx.com says...

> 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.

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

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

Geoff M

[Steve]

On Thu, 02 Mar 2006 14:38:20 +0800, no.mail.pls wrote:

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.databases.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?

Could it be that it's a new group?

[Jerry Stuckle]

no.mail.pls wrote:

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.databases.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" <ge***********@trap.gmail.com> wrote in message
news:MP************************@news.readfreenews. net...

bo*******@planetx.com says...

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.

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

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

Geoff M

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.mysql, 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.
js*******@attglobal.net
==================

[Jerry Stuckle]

Bosconian wrote:

"no.mail.pls" <no*****@st.peters> wrote in message
news:44***********************@news.sunsite.dk...

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" <Th*****@Aint.Valid> wrote in message
news:pa****************************@Aint.Valid.. .

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
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

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

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

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.
js*******@attglobal.net
==================

[Erwin Moller]

no.mail.pls wrote:

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?

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]

Hi,

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

cheers

"Erwin Moller"
<si******************************************@spam yourself.com> wrote in
message news:44***********************@news.xs4all.nl...

no.mail.pls wrote:

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?

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