Grouping & Counting

[zozew]

hi a quicky Question regarding grouping and counting...

Im counting inmates per Squad in tblInmateProfile in the query below. As well as a few other fields but i cant get the records to group and count properly. just counting the Totals per squad is easy but then counting the "Sub" counts is tricky...

This SQL

SELECT DISTINCTROW tblInmateProfile.tblsquad_ID, Count(*) AS [InmatesTot], tblInmateProfile.tblclass_ID, tblInmateProfile.medicalConfinment, tblInmateProfile.jailStatus
FROM tblInmateProfile
WHERE (((tblInmateProfile.tblstatus_ID)=1) AND ((tblInmateProfile.tblclass_ID)=3))
GROUP BY tblInmateProfile.tblsquad_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.gender, tblInmateProfile.medicalConfinment, tblInmateProfile.jailStatus
HAVING (((tblInmateProfile.gender)=1))
ORDER BY tblInmateProfile.tblclass_ID

Gives me this kind of result...

squad             InmatesTot           class    medicalConfinment    jailStatus
    2                  60               3                              False
    2                  2                3             1                False
    2                  2                3             2                False
    3                  7                3                              False
    15                 64               3                              False
    15                 2                3             1                False
    15                 3                3             2                False
    16                 10               3                              true
    16                 1                3             2                False
    17                 90               3                              False
    17                 1                3             1                False
    20                 9                3                              False
    20                 1                3             1                False
    21                 239              3                              true
    21                 7                3             1                False
    21                 3                3             2                False
    22                 174              3                              False
    22                 4                3             1                False
    22                 1                3             2                False

But i would like to group them all by the Squad and split the medical Confinement into two columns as it can have two values 1 or 2 (and null). The JailStatus is a Boolean and i would like to count that as well.

I would like the result to look like this below. I can get that if i only count the Squads but adding the other fields thats when i mess up things...

squad           InmatesTot          class    medicalConfinment1  medicalConfinment2     jailStatus
    2                  64               3             2                     2                  0
    3                  7                3                                                      0
    15                 69               3             2                     3                  0
    16                 11               3                                   1                  3
    17                 91               3             1                                        0
    20                 10               3             1                                        0
    21                 249              3             7                     3                  4
    22                 179              3             4                     1                  0

 

[Galaxiom]

Do the group and count first in one query then join these results back to the other data you want to include.

 

[arnelgp]

SELECT DISTINCTROW tblInmateProfile.tblsquad_ID, Count(tblInmateProfile.tblSquad_ID) AS InMatesTot, tblInmateProfile.tblclass_ID, Sum(IIf(Nz([medicalConfinment],0)<2,[MedicalConfinMent],Null)) AS medicalConfinment1, Sum(IIf(Nz([medicalConfinment],0)>=2,[MedicalConfinMent],Null)) AS medicalConfinment2, tblInmateProfile.jailStatus
FROM tblInmateProfile
WHERE (((tblInmateProfile.tblstatus_ID)=1) AND ((tblInmateProfile.tblclass_ID)=3))
GROUP BY tblInmateProfile.tblsquad_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.jailStatus, tblInmateProfile.tblclass_ID, tblInmateProfile.gender
HAVING (((tblInmateProfile.gender)=1))
ORDER BY tblInmateProfile.tblclass_ID;

 

[zozew]

SELECT DISTINCTROW tblInmateProfile.tblsquad_ID, Count(tblInmateProfile.tblSquad_ID) AS InMatesTot, tblInmateProfile.tblclass_ID, Sum(IIf(Nz([medicalConfinment],0)<2,[MedicalConfinMent],Null)) AS medicalConfinment1, Sum(IIf(Nz([medicalConfinment],0)>=2,[MedicalConfinMent],Null)) AS medicalConfinment2, tblInmateProfile.jailStatus
FROM tblInmateProfile
WHERE (((tblInmateProfile.tblstatus_ID)=1) AND ((tblInmateProfile.tblclass_ID)=3))
GROUP BY tblInmateProfile.tblsquad_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.jailStatus, tblInmateProfile.tblclass_ID, tblInmateProfile.gender
HAVING (((tblInmateProfile.gender)=1))
ORDER BY tblInmateProfile.tblclass_ID;

HAHA almost perfect medicalConfinment 1 and 2 are superb!! But im getting the JailStatus column showing the checkbox not the sum of records for each squad with the Jailstatus set to true...

 

[arnelgp]

SELECT DISTINCTROW tblInmateProfile.tblsquad_ID, Count(tblInmateProfile.tblSquad_ID) AS InMatesTot, tblInmateProfile.tblclass_ID, Sum(IIf(Nz([medicalConfinment],0)<2,[MedicalConfinMent],Null)) AS medicalConfinment1, Sum(IIf(Nz([medicalConfinment],0)>=2,[MedicalConfinMent],Null)) AS medicalConfinment2, Sum(Abs([jailStatus])) AS jailStat
FROM tblInmateProfile
WHERE (((tblInmateProfile.tblstatus_ID)=1) AND ((tblInmateProfile.tblclass_ID)=3))
GROUP BY tblInmateProfile.tblsquad_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.gender
HAVING (((tblInmateProfile.gender)=1))
ORDER BY tblInmateProfile.tblclass_ID;

 

[zozew]

Ahh thanks again you really helped me as i tested a bit and maybe my explanation was a bit fuzzy but the SUM function you use actually adds the values in the fields i wanted to count them. But no matter i just changed your SUM for COUNT and TATA!!! it all worked. I have a few other fields i count so i added them and this is how it all looks

SELECT DISTINCTROW tblInmateProfile.tblsquad_ID, Count(IIf(Nz([jailstatus],0)=True,[jailstatus],Null)) AS JailS, Count(IIf(Nz([jailAide],"")=3,[jailAide],Null)) AS JailWard, Count(IIf(Nz([medicalConfinment],0)=1,[MedicalConfinMent],Null)) AS [Hospital A], Count(IIf(Nz([medicalConfinment],0)=2,[MedicalConfinMent],Null)) AS [Q-Ward], Count(IIf(Nz([consultation],0)=True,[consultation],Null)) AS Consult, Count(tblInmateProfile.tblSquad_ID) AS InMatesTot
FROM tblInmateProfile
WHERE (((tblInmateProfile.tblstatus_ID)=1) AND ((tblInmateProfile.tblclass_ID)=3))
GROUP BY tblInmateProfile.tblsquad_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.gender
HAVING (((tblInmateProfile.gender)=1))
ORDER BY tblInmateProfile.tblclass_ID;

So tons of thanks!!!

 

[arnelgp]

yes count, but i think the outcome are the same whether we use Count or Sum on boolean field. on Count you only wanted to count those with True:

Field Values: True, True, False, True, False, True, True
Numeric Equiv: -1 , -1, , 0 , -1, , 0, -1, -1
Abs(): 1, 1, , 0 , 1, , 0, 1, 1
Count: 5
Sum: 5

 

[zozew]

Yes I noticed that it was the same on boolean values but not on the medicalconfinment and I have another field called jailaide that can have up to 4 different values from 1 to 4..but in any case your examples made it possible let for me to figure out how to solve it. I can't thank you enough

 

[zozew]

Do the group and count first in one query then join these results back to the other data you want to include.

Not really sure how you mean, but thanks for helping

 

[zozew]

Follow up Question:

Can I somehow make a Column calculation like this in the QueryDesign mode

Formation: [InMatesTot]-[JailS]-[JailWard]-[Hospital A]-[Q-Ward]-[Consult]

For this Query

SELECT DISTINCTROW tblInmateProfile.tblsquad_ID, Count(IIf(Nz([jailstatus],0)=True,[jailstatus],Null)) AS JailS, Count(IIf(Nz([jailAide],"")=3,[jailAide],Null)) AS JailWard, Count(IIf(Nz([medicalConfinment],0)=1,[MedicalConfinMent],Null)) AS [Hospital A], Count(IIf(Nz([medicalConfinment],0)=2,[MedicalConfinMent],Null)) AS [Q-Ward], Count(IIf(Nz([consultation],0)=True,[consultation],Null)) AS Consult, Count(tblInmateProfile.tblSquad_ID) AS InMatesTot
FROM tblInmateProfile
WHERE (((tblInmateProfile.tblstatus_ID)=1) AND ((tblInmateProfile.tblclass_ID)=3))
GROUP BY tblInmateProfile.tblsquad_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.tblclass_ID, tblInmateProfile.gender
HAVING (((tblInmateProfile.gender)=1))
ORDER BY tblInmateProfile.tblclass_ID;

I want to take the InmateTot minus all the "Count" columns made and put it in a "Formations" column

thanks again for any help

 

[zozew]

Follow up Question:

Can I somehow make a Column calculation like this in the QueryDesign mode

Im a little slow sometimes...my expression at the topped worked...i just forgot to change the automatic "Group By" to "Expression" in the query designer and it all worked...So thanks so much for all the help..again haha!!