Solved: SQL: Need to nest multiple select queries

[Ken Puls]

I'm sure that this is an easy one, but I've tried with a Union query and a Left Join, and I keep getting an error telling me that there is a syntax error in my From clause... probably because there is. LOL!

SELECT CatagoryNames, Sum(CountOfCATEGORIES) as SUMOFCATS
FROM UNION ((SELECT [01_CLUB_MemberMaster].CATEGORY_LINE_1 AS CatagoryNames, Count([01_CLUB_MemberMaster].CATEGORY_LINE_1) AS CountOfCATEGORIES
FROM 01_CLUB_MemberMaster GROUP BY [01_CLUB_MemberMaster].CATEGORY_LINE_1 ORDER BY [01_CLUB_MemberMaster].CATEGORY_LINE_1),
(SELECT [01_CLUB_MemberMaster].CATEGORY_LINE_2 AS CatagoryNames, Count([01_CLUB_MemberMaster].CATEGORY_LINE_2) AS CountOfCATEGORIES
FROM 01_CLUB_MemberMaster GROUP BY [01_CLUB_MemberMaster].CATEGORY_LINE_2 ORDER BY [01_CLUB_MemberMaster].CATEGORY_LINE_2))
GROUP BY CatagoryNames
ORDER BY CatagoryNames;

I'm working with a vendor's DB which has a fairly poor setup, IMHO. There are about 24 catagories columns for each person. I need a total count of specific catagories, but I could have say "golf dues" in any one of those 24 catagory fields. I'm trying to build one summarized list of them.

I know that this does work:
SELECT CatagoryNames, Sum(CountOfCatagories) as SumOfCats
FROM (SELECT [01_CLUB_MemberMaster].CATEGORY_LINE_1 AS CatagoryNames, Count([01_CLUB_MemberMaster].CATEGORY_LINE_1) AS CountOfCatagories
FROM 01_CLUB_MemberMaster GROUP BY [01_CLUB_MemberMaster].CATEGORY_LINE_1 ORDER BY [01_CLUB_MemberMaster].CATEGORY_LINE_1)
GROUP BY CatagoryNames
ORDER BY CatagoryNames;

As does this:
SELECT CatagoryNames, Sum(CountOfCatagories) as SumOfCats
FROM (SELECT [01_CLUB_MemberMaster].CATEGORY_LINE_2 AS CatagoryNames, Count([01_CLUB_MemberMaster].CATEGORY_LINE_2) AS CountOfCatagories
FROM 01_CLUB_MemberMaster GROUP BY [01_CLUB_MemberMaster].CATEGORY_LINE_2 ORDER BY [01_CLUB_MemberMaster].CATEGORY_LINE_2)
GROUP BY CatagoryNames
ORDER BY CatagoryNames;

But how do I marry them together? As I say, there'll be 24 nested that I need in a master list.

I know that this should be simple, but my SQL is weak. :(

[Norie]

Ken

I'm not 100% sure if this is what you want but this is how I would set up a UNION query for those 2 queries.

I don't get any syntax error but can't test it further as I don't have a suitable setup.

SELECT CatagoryNames, Sum(CountOfCatagories) As SumOfCats
FROM (SELECT [01_CLUB_MemberMaster].CATEGORY_LINE_1 As CatagoryNames, Count([01_CLUB_MemberMaster].CATEGORY_LINE_1) As CountOfCatagories
FROM 01_CLUB_MemberMaster GROUP BY [01_CLUB_MemberMaster].CATEGORY_LINE_1 ORDER BY [01_CLUB_MemberMaster].CATEGORY_LINE_1)
GROUP BY CatagoryNames
ORDER BY CatagoryNames;
UNION
SELECT CatagoryNames, Sum(CountOfCatagories) As SumOfCats
FROM (SELECT [01_CLUB_MemberMaster].CATEGORY_LINE_2 As CatagoryNames, Count([01_CLUB_MemberMaster].CATEGORY_LINE_2) As CountOfCatagories
FROM 01_CLUB_MemberMaster GROUP BY [01_CLUB_MemberMaster].CATEGORY_LINE_2 ORDER BY [01_CLUB_MemberMaster].CATEGORY_LINE_2)
GROUP BY CatagoryNames
ORDER BY CatagoryNames;

[Ken Puls]

Hi Norie,

Your version actually just comines the queries, but does not add them together. It seems like I've got some redundancy in here, but this will do it:

SELECT CatagoryNames, Sum(SumOfCats) As TotalCount From (
SELECT CatagoryNames, Sum(CountOfCatagories) As SumOfCats
FROM (SELECT [01_CLUB_MemberMaster].CATEGORY_LINE_1 As CatagoryNames, Count([01_CLUB_MemberMaster].CATEGORY_LINE_1) As CountOfCatagories
FROM 01_CLUB_MemberMaster GROUP BY [01_CLUB_MemberMaster].CATEGORY_LINE_1 ORDER BY [01_CLUB_MemberMaster].CATEGORY_LINE_1)
GROUP BY CatagoryNames
ORDER BY CatagoryNames
UNION
SELECT CatagoryNames, Sum(CountOfCatagories) As SumOfCats
FROM (SELECT [01_CLUB_MemberMaster].CATEGORY_LINE_2 As CatagoryNames, Count([01_CLUB_MemberMaster].CATEGORY_LINE_2) As CountOfCatagories
FROM 01_CLUB_MemberMaster GROUP BY [01_CLUB_MemberMaster].CATEGORY_LINE_2 ORDER BY [01_CLUB_MemberMaster].CATEGORY_LINE_2)
GROUP BY CatagoryNames)
GROUP BY CatagoryNames
ORDER BY CatagoryNames;

Thanks!