Creating an SQL query with multiple counts and different criteria from one table

debugcn Published at Dev

cardycakes

I have an Access DB I have coded in VBA which my team use to log all of our requests. I want to create a query which will pull multiple counts from a single table with different criteria. An example of the table follows:

    Table TLogging
    logTypeId - Int [ this is 0 - 5 ] 
    isResolved - Boolean [ this is stored as -1 for true, 0 for false ]

I want to create a query which will output something like the following:

    Resolved, LogType1, LogType2
    True      101       39
    False     49        104
    All       150       144

So I want:

    Count of LogType1 where isResolved = True
    Count of LogType1 where isResolved = False
    Count of LogType1 where isResolved = True OR isResolved = False

And the same for LogType2.

I have already searched SO and other forums, but found no solutions. Also, CASE WHEN does not work in Access VBA.

Thanks in advance for your help!

Jean Robert

You can start with something like this:

SELECT logTypeId, Count(logTypeId) AS nbTotal, -1 * Sum(isResolved) AS nbTrue, Count(logTypeId) + Sum(isResolved) As nbFalse
FROM TLogging
GROUP BY logTypeId;

It gives about the same result as you want, but transposed.

EDIT: or this, it's ugly but i think it gives exactly the result you want

SELECT 'True' As Resolved, t0.nb As LogType0, t1.nb As LogType1, t2.nb As LogType2, t3.nb As LogType3, t4.nb As LogType4, t5.nb As LogType5
FROM (Select -1 * Sum(isResolved) As nb From TLogging Where logTypeId = 0)  AS t0
    , (Select -1 * Sum(isResolved) As nb From TLogging Where logTypeId = 1)  AS t1
    , (Select -1 * Sum(isResolved) As nb From TLogging Where logTypeId = 2)  AS t2
    , (Select -1 * Sum(isResolved) As nb From TLogging Where logTypeId = 3)  AS t3
    , (Select -1 * Sum(isResolved) As nb From TLogging Where logTypeId = 4)  AS t4
    , (Select -1 * Sum(isResolved) As nb From TLogging Where logTypeId = 5)  AS t5
Union All
SELECT 'False' As Resolved, t0.nb As LogType0, t1.nb As LogType1, t2.nb As LogType2, t3.nb As LogType3, t4.nb As LogType4, t5.nb As LogType5
FROM (Select Count(*) + Sum(isResolved) As nb From TLogging Where logTypeId = 0)  AS t0
    , (Select Count(*) + Sum(isResolved) As nb From TLogging Where logTypeId = 1)  AS t1
    , (Select Count(*) + Sum(isResolved) As nb From TLogging Where logTypeId = 2)  AS t2
    , (Select Count(*) + Sum(isResolved) As nb From TLogging Where logTypeId = 3)  AS t3
    , (Select Count(*) + Sum(isResolved) As nb From TLogging Where logTypeId = 4)  AS t4
    , (Select Count(*) + Sum(isResolved) As nb From TLogging Where logTypeId = 5)  AS t5
Union All
SELECT 'All' As Resolved, t0.nb As LogType0, t1.nb As LogType1, t2.nb As LogType2, t3.nb As LogType3, t4.nb As LogType4, t5.nb As LogType5
FROM (Select Count(*) As nb From TLogging Where logTypeId = 0)  AS t0
    , (Select Count(*) As nb From TLogging Where logTypeId = 1)  AS t1
    , (Select Count(*) As nb From TLogging Where logTypeId = 2)  AS t2
    , (Select Count(*) As nb From TLogging Where logTypeId = 3)  AS t3
    , (Select Count(*) As nb From TLogging Where logTypeId = 4)  AS t4
    , (Select Count(*) As nb From TLogging Where logTypeId = 5)  AS t5
;

Collected from the Internet

Please contact [email protected] to delete if infringement.