Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
count same field with different statuscode |
Thu, Nov 6 2014 3:14 PM | Permanent Link |
Mike | 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 |
Thu, Nov 6 2014 5:20 PM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Thu, Nov 6 2014 7:17 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Fri, Nov 7 2014 3:00 AM | Permanent Link |
Mike | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |