UNION SELECT problem

[sbbrown9924]

What am I doing wrong here? I am doing a bunch of UNION SELECTS so that I can count the results of a checkbox field. This query only gives me back the results of counting the first condition [ctQ1comer].

SELECT COUNT(Q1) AS [ctQ1comer]
FROM Survey WHERE Q1=1 UNION
SELECT COUNT(Q1) AS [ctQ1mitchell] FROM Survey WHERE Q1=2 UNION
SELECT COUNT(Q1) AS [ctQ1dcam] FROM Survey WHERE Q1=3

 

[lespaul]

well, you are only returning a SINGLE field in each query so you can't name them differently.

I think what you want is:

SELECT "ctQ1comer" As QuestionTitle, COUNT(Q1) FROM Survey WHERE Q1=1
UNION
SELECT "ctQ1mitchell", COUNT(Q1) FROM Survey WHERE Q1=2
UNION
SELECT "ctQ1dcam", COUNT(Q1) AS FROM Survey WHERE Q1=3




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[sbbrown9924]

lespaul;

Can you place the alias name first in a SELECT statement? ctQ1comer, ctQ1mitchell and ctQ1dcam are all aliases.

I am getting an error message: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctutation is incorrect.

 

[lespaul]

ok your original query without the additional aliases:

SELECT COUNT(Q1) AS [ctQ1comer]
FROM Survey WHERE Q1=1 UNION
SELECT COUNT(Q1) FROM Survey WHERE Q1=2 UNION
SELECT COUNT(Q1) FROM Survey WHERE Q1=3

should return a dataset that looks like:

ctQ1comer
25
36
84

what do you want your dataset to return? Not sure why you are getting an error in the UNION query I provided, the only thing I can think of is it's missing the GROUP BY clause, but I wouldn't expect the error you got if that was the problem.

 

[sbbrown9924]

I'd like the dataset to count up all responses to this online survey. I should also mention I am developing this in Dreamweaver 2004. I'm getting the results from their test function.

Q1: What hospital do you work at?

Comer [checkboxQ1]
Mitchell [checkboxQ1]
DCAM [checkboxQ1]
..... [checkboxQ1]

Q2: blah blah blah....

I tried to group by but I got the same error message.

 

[cmmrfrds]

A Union query condenses like rows. If you want all rows do a "Union ALL".

 

[lespaul]

So, when Q1 = 1 then the answer was Comer, when Q1 = 2 the answer was Mitchell when Q1 = 3 then answer was DCAM, right? So the table Survey has a field named Q1 and the values in that field are 1, 2, 3 etc.

The easiest thing is:

SELECT Q1, Count(*) FROM SURVEY GROUP BY Q1

this will return
1 25
2 36
3 84


Do you have a table that lists 1 = Comer, 2 = Mitchell, etc?

If so you can join into that table to get:

SELECT HospitalName, Count(Q1) FROM SURVEY INNER JOIN HospitalNameTable on Survey.Q1 = HospitalNameTable.IDField
GROUP BY HospitalName

Comer 25
Mitchell 36
DCAM 84

Does that help?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design