August 10, 2017 at 7:07 am
Hello guys,
I have 3 tables APVS_Templates, APVS_Template_Sections and APVS_Bus_Sections, All tables has the TemplateID as reference
I am trying to create a query that get me the following columns
Template name, TotalNo.Sections, TotalSectionAnswers, %of Answers
(SELECT APVS_Templates.Template_Name,COUNT(APVS_Bus_Sections.Section_ID) AS NumberOfAnswers FROM APVS_Templates
LEFT JOIN APVS_Bus_Sections ON APVS_Bus_Sections.Template_ID = APVS_Templates.Template_ID GROUP BY Template_Name)
UNION
(SELECT APVS_Templates.Template_Name As Temp2,COUNT(APVS_Template_Sections.Section_ID) AS NumberOfQuestions FROM APVS_Templates
LEFT JOIN APVS_Template_Sections ON APVS_Template_Sections.Template_ID = APVS_Templates.Template_ID GROUP BY Template_Name)
The queries i am using working independently but when i use the union it is adding the second query as ROWS and NOT as Columns
Thanks for your help
August 10, 2017 at 7:09 am
hayekmichel - Thursday, August 10, 2017 7:07 AMwhen i use the union it is adding the second query as ROWS and NOT as Columns
Thanks for your help
That's what UNION does.
If you want to add the second query as columns, you need to join the two queries together (join clause). Union does exactly what you're seeing, add the second query's results as rows to the first query's results.
Maybe something like SELECT s1.Template_Name,
s1.NumberOfAnswers,
s2.NumberOfQuestions
FROM (SELECT APVS_Templates.Template_Name,
COUNT(APVS_Bus_Sections.Section_ID) AS NumberOfAnswers
FROM APVS_Templates
LEFT JOIN APVS_Bus_Sections ON APVS_Bus_Sections.Template_ID = APVS_Templates.Template_ID
GROUP BY Template_Name
) s1
INNER JOIN (SELECT APVS_Templates.Template_Name,
COUNT(APVS_Template_Sections.Section_ID) AS NumberOfQuestions
FROM APVS_Templates
LEFT JOIN APVS_Template_Sections ON APVS_Template_Sections.Template_ID = APVS_Templates.Template_ID
GROUP BY Template_Name
) s2 ON s1.Template_Name = s2.Template_Name;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2017 at 7:15 am
UNION is working exactly as intended. UNION doesn't add columns to a dataset, it adds further rows.
Without knowing your data this is a guess, but perhaps this is the query you are actually looking for:SELECT T.Template_Name,
COUNT(DISTINCT BS.Section_ID) AS NumberOfAnswers,
COUNT(DISTINCT TS.Section_ID) AS NumberOfQuestions
FROM APVS_Templates T
LEFT JOIN APVS_Template_Sections TS ON T.Template_ID = TS.Template_ID
LEFT JOIN APVS_Bus_Sections BS ON T.Template_ID = BS.Template_ID
GROUP BY T.Template_Name;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 7:52 am
Thom A - Thursday, August 10, 2017 7:15 AMUNION is working exactly as intended. UNION doesn't add columns to a dataset, it adds further rows.Without knowing your data this is a guess, but perhaps this is the query you are actually looking for:
SELECT T.Template_Name,
COUNT(DISTINCT BS.Section_ID) AS NumberOfAnswers,
COUNT(DISTINCT TS.Section_ID) AS NumberOfQuestions
FROM APVS_Templates T
LEFT JOIN APVS_Template_Sections TS ON T.Template_ID = TS.Template_ID
LEFT JOIN APVS_Bus_Sections BS ON T.Template_ID = BS.Template_ID
GROUP BY T.Template_Name;
I'd be careful on using this as it might give incorrect counts. As you mention, it all depends on the data. I'd usually use something like this:
SELECT T.Template_Name,
A.NumberOfAnswers,
Q.NumberOfQuestions
FROM APVS_Templates T
OUTER APPLY(
SELECT COUNT(APVS_Bus_Sections.Section_ID) AS NumberOfAnswers
FROM APVS_Bus_Sections
WHERE APVS_Bus_Sections.Template_ID = T.Template_ID) A
OUTER APPLY(
SELECT COUNT(APVS_Template_Sections.Section_ID) AS NumberOfQuestions
FROM APVS_Template_Sections
WHERE APVS_Template_Sections.Template_ID = T.Template_ID) Q;
SELECT T.Template_Name,
ISNULL( A.NumberOfAnswers, 0) AS NumberOfAnswers,
ISNULL( Q.NumberOfQuestions, 0) AS NumberOfQuestions
FROM APVS_Templates T
LEFT JOIN(
SELECT bs.Template_ID, COUNT(bs.Section_ID) AS NumberOfAnswers
FROM APVS_Bus_Sections bs
GROUP BY bs.Template_ID) A ON A.Template_ID = T.Template_ID
LEFT JOIN(
SELECT ts.Template_ID, COUNT(ts.Section_ID) AS NumberOfQuestions
FROM APVS_Template_Sections ts
GROUP BY ts.Template_ID) Q ON Q.Template_ID = T.Template_ID;
August 10, 2017 at 8:05 am
Luis Cazares - Thursday, August 10, 2017 7:52 AMI'd be careful on using this as it might give incorrect counts. As you mention, it all depends on the data. I'd usually use something like this:
Totally agree, that it might not work. It was a guess. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 10, 2017 at 8:20 am
Luis Cazares - Thursday, August 10, 2017 7:52 AMI'd be careful on using this as it might give incorrect counts. As you mention, it all depends on the data. I'd usually use something like this:
SELECT T.Template_Name,
A.NumberOfAnswers,
Q.NumberOfQuestions
FROM APVS_Templates T
OUTER APPLY(
SELECT COUNT(APVS_Bus_Sections.Section_ID) AS NumberOfAnswers
FROM APVS_Bus_Sections
WHERE APVS_Bus_Sections.Template_ID = T.Template_ID) A
OUTER APPLY(
SELECT COUNT(APVS_Template_Sections.Section_ID) AS NumberOfQuestions
FROM APVS_Template_Sections
WHERE APVS_Template_Sections.Template_ID = T.Template_ID) Q;SELECT T.Template_Name,
ISNULL( A.NumberOfAnswers, 0) AS NumberOfAnswers,
ISNULL( Q.NumberOfQuestions, 0) AS NumberOfQuestions
FROM APVS_Templates T
LEFT JOIN(
SELECT bs.Template_ID, COUNT(bs.Section_ID) AS NumberOfAnswers
FROM APVS_Bus_Sections bs
GROUP BY bs.Template_ID) A ON A.Template_ID = T.Template_ID
LEFT JOIN(
SELECT ts.Template_ID, COUNT(ts.Section_ID) AS NumberOfQuestions
FROM APVS_Template_Sections ts
GROUP BY ts.Template_ID) Q ON Q.Template_ID = T.Template_ID;
SELECT T.Template_Name,
ISNULL( A.NumberOfAnswers, 0) AS NumberOfAnswers,
ISNULL( Q.NumberOfQuestions, 0) AS NumberOfQuestions
FROM APVS_Templates T
LEFT JOIN(
SELECT bs.Template_ID, COUNT(bs.Section_ID) AS NumberOfAnswers
FROM APVS_Bus_Sections bs
GROUP BY bs.Template_ID) A ON A.Template_ID = T.Template_ID
LEFT JOIN(
SELECT ts.Template_ID, COUNT(ts.Section_ID) AS NumberOfQuestions
FROM APVS_Template_Sections ts
GROUP BY ts.Template_ID) Q ON Q.Template_ID = T.Template_ID;
Luiz that did the trick
Thank you so much
August 10, 2017 at 8:23 am
hayekmichel - Thursday, August 10, 2017 8:20 AM
SELECT T.Template_Name,
ISNULL( A.NumberOfAnswers, 0) AS NumberOfAnswers,
ISNULL( Q.NumberOfQuestions, 0) AS NumberOfQuestions
FROM APVS_Templates T
LEFT JOIN(
SELECT bs.Template_ID, COUNT(bs.Section_ID) AS NumberOfAnswers
FROM APVS_Bus_Sections bs
GROUP BY bs.Template_ID) A ON A.Template_ID = T.Template_ID
LEFT JOIN(
SELECT ts.Template_ID, COUNT(ts.Section_ID) AS NumberOfQuestions
FROM APVS_Template_Sections ts
GROUP BY ts.Template_ID) Q ON Q.Template_ID = T.Template_ID;Luiz that did the trick
Thank you so much
You're welcome.
Do you understand why and how it works?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply