469,575 Members | 1,608 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

Mysql count(condition)?

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

Mar 2 '06 #1
14 6365
"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
Mar 2 '06 #2
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

Mar 2 '06 #3
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

Mar 2 '06 #4
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

Mar 2 '06 #5
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
==================
Mar 2 '06 #6
"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. :-]
Mar 2 '06 #7
"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.
Mar 2 '06 #8
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
Mar 2 '06 #9
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

Mar 2 '06 #10
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?
Mar 2 '06 #11
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
==================
Mar 2 '06 #12
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
==================
Mar 2 '06 #13
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
Mar 2 '06 #14
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

Mar 3 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by 'bonehead | last post: by
6 posts views Thread by Xenophobe | last post: by
1 post views Thread by denisb | last post: by
2 posts views Thread by jaysonsch | last post: by
reply views Thread by Graham Simms | last post: by
6 posts views Thread by Ljoha | last post: by
3 posts views Thread by Zeeshan | last post: by
22 posts views Thread by MP | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.