group by and count stuff

i need a bit of sql that will tell me how many jobs are at which status

customer table
jobs table (each job has an id number field)


output something like:

customer_id - job status 0 - job status 1 - job status 2
1 0 1 0
2 1 0 2

last thing i came up with is:

SELECT customer_name_3.customer_name_3_id, count (if(job_status_id = 0, 1,
Null)) AS job_status_id_0
FROM `customer_name_3`
LEFT JOIN `jobs` ON customer_name_3.customer_name_3_id =
jobs.customer_name_3_id
GROUP BY customer_name_3.customer_name_3_id

which doesn't work (since i suck)
can someone point me in the right direction, is there a better way to do
this ?

On 21 Feb, 15:52, "Geoff" <foooooo...@hotmail.com> wrote:
> i need a bit of sql that will tell me how many jobs are at which status
>
> customer table
> jobs table (each job has an id number field)
>
> output something like:
>
> customer_id - job status 0 - job status 1 - job status 2
> 1 0 1 0
> 2 1 0 2
>
> last thing i came up with is:
>
> SELECT customer_name_3.customer_name_3_id, count (if(job_status_id = 0, 1,
> Null)) AS job_status_id_0
> FROM `customer_name_3`
> LEFT JOIN `jobs` ON customer_name_3.customer_name_3_id =
> jobs.customer_name_3_id
> GROUP BY customer_name_3.customer_name_3_id
>
> which doesn't work (since i suck)
> can someone point me in the right direction, is there a better way to do
> this ?

"which doesn't work"
Very useful, very informative NOT.

Help us to help you!
Tel us precisely what in this query doesn't work.

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:1172075993.246988.292280@q2g2000cwa.googlegro ups.com...
> On 21 Feb, 15:52, "Geoff" <foooooo...@hotmail.com> wrote:
>> i need a bit of sql that will tell me how many jobs are at which status
>>
>> customer table
>> jobs table (each job has an id number field)
>>
>> output something like:
>>
>> customer_id - job status 0 - job status 1 - job status 2
>> 1 0 1 0
>> 2 1 0 2
>>
>> last thing i came up with is:
>>
>> SELECT customer_name_3.customer_name_3_id, count (if(job_status_id = 0,
>> 1,
>> Null)) AS job_status_id_0
>> FROM `customer_name_3`
>> LEFT JOIN `jobs` ON customer_name_3.customer_name_3_id =
>> jobs.customer_name_3_id
>> GROUP BY customer_name_3.customer_name_3_id
>>
>> which doesn't work (since i suck)
>> can someone point me in the right direction, is there a better way to do
>> this ?
>
> "which doesn't work"
> Very useful, very informative NOT.
>
> Help us to help you!
> Tel us precisely what in this query doesn't work.
>

sorry my head was inside out somewhat when i wrote that
hopefully this is a bit clearer

i have a jobs table something like:

job_id customer_id job_status_id
1 1 0
2 1 1
3 2 2
4 2 2

hoping for output of:

customer_id status_id_0 status_id_1 status_id_2
1 1 1 0
2 0 0 2

php frontend, so i could use that to code instead of getting mysql to do it
it's just so i can show how many jobs are at which status by customer

On 22 Feb, 10:22, "Geoff" <foooooo...@hotmail.com> wrote:
> "Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
>
> news:1172075993.246988.292280@q2g2000cwa.googlegro ups.com...
>
>
>
>
>
> > On 21 Feb, 15:52, "Geoff" <foooooo...@hotmail.com> wrote:
> >> i need a bit of sql that will tell me how many jobs are at which status
>
> >> customer table
> >> jobs table (each job has an id number field)
>
> >> output something like:
>
> >> customer_id - job status 0 - job status 1 - job status 2
> >> 1 0 1 0
> >> 2 1 0 2
>
> >> last thing i came up with is:
>
> >> SELECT customer_name_3.customer_name_3_id, count (if(job_status_id = 0,
> >> 1,
> >> Null)) AS job_status_id_0
> >> FROM `customer_name_3`
> >> LEFT JOIN `jobs` ON customer_name_3.customer_name_3_id =
> >> jobs.customer_name_3_id
> >> GROUP BY customer_name_3.customer_name_3_id
>
> >> which doesn't work (since i suck)
> >> can someone point me in the right direction, is there a better way to do
> >> this ?
>
> > "which doesn't work"
> > Very useful, very informative NOT.
>
> > Help us to help you!
> > Tel us precisely what in this query doesn't work.
>
> sorry my head was inside out somewhat when i wrote that
> hopefully this is a bit clearer
>
> i have a jobs table something like:
>
> job_id customer_id job_status_id
> 1 1 0
> 2 1 1
> 3 2 2
> 4 2 2
>
> hoping for output of:
>
> customer_id status_id_0 status_id_1 status_id_2
> 1 1 1 0
> 2 0 0 2
>
> php frontend, so i could use that to code instead of getting mysql to do it
> it's just so i can show how many jobs are at which status by customer- Hide quoted text -
>
> - Show quoted text -

Look, you posted a query.
You didn't post a create table schema or any sample data inserts, so I
have no way to find out what in the query you posted "doesn't work".
So I asked you quite clearly "Tell us precisely what in this query
doesn't work."

Have you answered the question? ......