----- Original Message -----
From: "Zack Vernon" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, March 19, 2006 12:11 PM
Subject: Counting Multiple Fields
Hello,
Would be grateful for any help!
I have a table like (in reality few more fields!!!):
Id Site Code Type of Job
What I want to do is make reports like:
Site Code Type of Job Number of Jobs for this type and
site
Number of jobs for this site Percent of the type of jobs for this
site
This is very unclear. I know that wrapping makes it harder to communicate
what you are trying to say but I'm not sure if you have listed three column
headings on the first line and then elaborated what they mean on the second
line or if you are listing six different columns that you want with
different contents. That makes the rest of your question very unclear too.
Guess it would be something like:
SELECT SiteCode, TypeOfJob, count(TypeOfJob) As countJobType FROM jobs
GROUP
BY SiteCode
But I cannot work out how to work out the number of jobs for the site.
Please help!
It sounds to me like you need to use count distinct. That can be used to
determine the number of different values in a column. For example, given
this table:
Site Job
---- ----
London Plumber
London Engineer
London Electrician
London Electrician
Paris Drywall Installer
Paris Architect
Paris Engineer
Venice Engineer
This query:
Select distinct site from mytable;
returns the different locations from the Site column:
Site
-----
London
Paris
Venice
This query:
select count(distinct site) from mytable;
returns the NUMBER of different sites in the table:
3
This query:
select distinct site, job from mytable;
returns the number of different combinations of sites and jobs. Note that
although there are two rows for electricians in London, the result contains
only one such row; the distinct eliminates the duplicate
combination of London and electrician.
Site Job
---- ----
London Plumber
London Engineer
London Electrician
Paris Drywall Installer
Paris Architect
Paris Engineer
Venice Engineer
If this information doesn't answer your question, please clarify what you
want.
Also, it is always a good idea to state which version of MySQL you are
using; older versions, which are still in widespread use, don't have all the
functionality of newer versions. That means that a solution that works fine
in a newer version may not work at all in an older version or may have to be
adapted to work.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
