Retrieve item types, their subtype, and counts of those types and subtypes

See below for 1st edit following the answer from @Gordon Linoff

See bottom for 2nd edit/messy solution

Original question

I have 3 tables;

tblOrganisations
tblOrganisationTypes
tblOrganisationSubTypes

An organisation can have a Type, and some Types can have a SubType

I'm trying to get a list and count of Types and SubTypes, but i'm having trouble with the count where a Type has a SubType.

The following query and code;

<CFQUERY NAME="RetrieveAllOrganisationTypes" DATASOURCE="#strDev#">
    SELECT tblOrganisations.tblOrganisationTypes_ReferenceID , COUNT(tblOrganisations.tblOrganisationTypes_ReferenceID) AS TypeCount , COUNT(tblOrganisations.tblOrganisationSubTypes_ReferenceID) AS SubTypeCount , tblOrganisationTypes.OrganisationType , tblOrganisationSubTypes.OrganisationSubType
            FROM (( tblOrganisations
            LEFT JOIN tblOrganisationTypes ON tblOrganisations.tblOrganisationTypes_ReferenceID = tblOrganisationTypes.ReferenceID )
            LEFT JOIN tblOrganisationSubTypes ON tblOrganisations.tblOrganisationSubTypes_ReferenceID = tblOrganisationSubTypes.ReferenceID )
            GROUP BY tblOrganisations.tblOrganisationTypes_ReferenceID , tblOrganisations.tblOrganisationSubTypes_ReferenceID
            ORDER BY tblOrganisationTypes.OrganisationType , tblOrganisationSubTypes.OrganisationSubType
</CFQUERY>

<CFOUTPUT QUERY="RetrieveAllOrganisationTypes" GROUP="OrganisationType">
    #OrganisationType# (#TypeCount#)<BR>
    <CFIF OrganisationSubType IS NOT ""><CFOUTPUT GROUP="OrganisationSubType">-- #OrganisationSubType# (#SubTypeCount#)<BR></CFOUTPUT></CFIF>
</CFOUTPUT>

Gets me this;

AFFILIATED (2)
ASSOCIATE (15)
FULL (10)
-- operator (10)
-- manufacturer (4)
-- owner (108)
-- survey company (4)
-- supplier (4)
GOVERNMENT (5)
MISCELLANEOUS (3)
SCIENCE (4)

But you see, the Full count - 10 - is wrong. It should be 130! For Types without a SubType the count is correct. The SubTypes count is correct. I've tried all sorts but i'm not getting anywhere :(

Any help will be greatly appreciated! :)

1st edit

Following the help from @Gordon Linoff, adding the WITH ROLLUP, removing the ORDER BY, and with the following code;

<CFOUTPUT QUERY="RetrieveAllOrganisationTypes" GROUP="OrganisationType">
    #OrganisationType# (#TypeCount#) #SubTypeCount#<BR>
    <CFIF OrganisationSubType IS NOT ""><CFOUTPUT GROUP="OrganisationSubType">-- #OrganisationSubType# (#TypeCount#) #SubTypeCount#<BR></CFOUTPUT></CFIF>
</CFOUTPUT>

I now get;

AFFILIATED (2) 2
ASSOCIATE (15) 15
FULL (10) 10
-- operator (10) 10
-- manufacturer (4) 4
-- owner (108) 108
-- survey company (4) 4
-- supplier (4) 4
-- (130) 130
GOVERNMENT (5) 5
MISCELLANEOUS (3) 3
SCIENCE (4) 4
(159) 159

So you see i am getting the 130, but how do i get the 130 by the FULL?!

2nd edit/messy solution

So i guess this is a kinda messy solution, but it works!

The following output code;

<CFOUTPUT QUERY="RetrieveAllOrganisationTypes" GROUP="OrganisationType">
    <CFOUTPUT GROUP="OrganisationSubType">
    <CFIF OrganisationSubType IS "" AND OrganisationType IS NOT "">#OrganisationType# (#TypeCount#)<BR></CFIF>
    </CFOUTPUT>
    <CFOUTPUT GROUP="OrganisationSubType">
    <CFIF OrganisationSubType IS NOT "" AND OrganisationType IS NOT "">--#OrganisationSubType# (#SubTypeCount#)<BR></CFIF>
    </CFOUTPUT>
</CFOUTPUT>

Gives me;

AFFILIATED (2)
ASSOCIATE (15)
FULL (130)
--operator (10)
--manufacturer (4)
--owner (108)
--survey company (4)
--supplier (4)
GOVERNMENT (5)
MISCELLANEOUS (3)
SCIENCE (4)

I'm sure there is probably a cleaner way to do this, and if anyone knows it i'm open to suggestions, but for now - this is working :)

Solution

This is your query with table aliases so it is a bit easier to read:

SELECT o.tblOrganisationTypes_ReferenceID ,     
       COUNT(o.tblOrganisationTypes_ReferenceID) AS TypeCount , 
       COUNT(o.tblOrganisationSubTypes_ReferenceID) AS SubTypeCount , 
       ot.OrganisationType , ost.OrganisationSubType
FROM tblOrganisations o LEFT JOIN
     tblOrganisationTypes ot
     ON o.tblOrganisationTypes_ReferenceID = ot.ReferenceID LEFT JOIN
     tblOrganisationSubTypes ost
     ON o.tblOrganisationSubTypes_ReferenceID = ost.ReferenceID )
GROUP BY o.tblOrganisationTypes_ReferenceID, 
         o.tblOrganisationSubTypes_ReferenceID
ORDER BY ot.OrganisationType , ost.OrganisationSubType;

You are confused. The "10" that you are getting is the number of organizations with FULL that have no subtypes. It is not some sort of aggregation. If you want sums at different levels, then a good approach uses ROLLUP:

GROUP BY o.tblOrganisationTypes_ReferenceID, 
         o.tblOrganisationSubTypes_ReferenceID WITH ROLLUP

This is going to produce additional rows for all possible subtotals (including a total for the whole table). You will need to figure out how to filter the resulting rows to get what you want. You should review the documentation on this feature.