Hi,

I have the following table.

Table fields/contents:
customer_id   project_id   status
1      5      2
2      6      1
1      11      1
3      3      2   

Possible statuscodes are:
"OK" : value 1
"DENIED" : value 2

And would like to have the following report:

CustomerID   Status=OK   Status=Denied
1      3      7
2      6      3
3      5      2   

Would this be possible with a query?

Regards,

Mike

Mike

Just to get clear:

You want
CustomerID, COUNT(Status = OK), COUNT(Status = Denied)

If that is the case it is not too hard to write, but I don't write this syntax often so I am not 100% whether this is correct ... hopefully it is ...

SELECT
 CustomerID,
 COUNT(IF(Status = 1 then Status else 0)) as StatusOK,
 COUNT(IF(Status = 2 then Status else 0)) as StatusDenied

FROM Table
GROUP BY CustomerID

On 7/11/2014 11:20 a.m., Adam Brett wrote:
> Mike
>
> Just to get clear:
>
> You want
> CustomerID, COUNT(Status = OK), COUNT(Status = Denied)
>
> If that is the case it is not too hard to write, but I don't write this syntax often so I am not 100% whether this is correct ... hopefully it is ...
>
> SELECT
>    CustomerID,
>    COUNT(IF(Status = 1 then Status else 0)) as StatusOK,
>    COUNT(IF(Status = 2 then Status else 0)) as StatusDenied
>
> FROM Table
> GROUP BY CustomerID
>

OR perhaps

 SELECT
    CustomerID,
    SUM(IF(Status = 1 then 1 else 0)) as StatusOK,
    SUM(IF(Status = 2 then 1 else 0)) as StatusDenied

 FROM Table
 GROUP BY CustomerID

Cheers

Jeff

Hi,

@Adam,
I have tried your example. All fields which were counted had the same results.

@Jeff,
This is working! Will extend the SQL a little more but this will do for now.

Thank you both for your help!

Regards,

Mike