Multiple Count in a View 1

[raniweb]

Hello,
I have a table with ten columns and I need to count each column for the data that equals column specific data. Is it possible to do this in one view? For example
COUNT(q3a) AS countq3a, q3a
FROM dbo.trainingsurveys
WHERE (q3a = N'8 AM to 10 AM')
GROUP BY q3a

That's one view but I would like to q3b, q3c all in the same view. Thank you!

 

[SQLSister]

And just how would you display the end result? Give sample data and results please.



Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[raniweb]

The result of that would be

countq3a q3a
15 8 AM to 10 AM

What I'm trying to do is

countq3a q3a countq3b q3b
15 8 AM to 10 AM 12 10 am to noon

And so on. I'm just trying to figure out if this is possible. Instead of doing queries for each question. Thank you!

 

[SQLSister]

And how would you know which set of answers to q3b relate to which ones in Q3a? You aren't thinking your problem through. If you do a group on Q3a and have 5 specific values for q3a that have a count, then you have 4 specific values for q3b that have a count how do they relate together?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[raniweb]

They don't really, I have one table that has q1, q2, q3a, q3b, q3c and so on. I have one view that I want to pull the count of each column into.

 

[SQLSister]

You still aren;t getting the point.

Take this case
Table
Q3a q3b q3c
a Null c
a c d
b b d
a a Null
Null c b

How would you want the answers displayed? If you want
Countq3a Countq3b countQ3c
4 4 4

This is relatively easy to do in one query, but not very useful as far as information goes. But if you want to know how many a, b answers to q3a, a, b, c answers to Q3b, and A,b, c, d answers to Q3c including 0's if there are no responses to a question, then you are talking something much more complex and you must define what the possible answers could be.



Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[raniweb]

Countq3a Countq2b Countq3c
4 4 4

That's exaclty what I want. I just am not able to put together all the counts in order to do that. Thank you!

 

[SQLSister]

Select Count(q3a) as CountQ3a, Count(q3b) as CountQ3b, Count(Q3c) as CountQ3c from Your Table

This doesn't work?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[raniweb]

No because the blanks are also counted, so I then specified the Criteria. For example

q3a q3b
8 to 10 10 to 1
8 to 10 blank/not null
8 to 10 10 to 1
blank/not null blank/not null
8 to 10 10 to 1

Select COUNT(q3a)
FROM dbo.trainingsurveys
WHERE (q3a = N'8 to 10')

Returns
q3a
4

I'm trying to get a return of

q3a q3b
4 3

Thank you again for all your help!

 

[SQLSister]

Then you will need to use a case statment as blank would count as a data entry in the field (and null does not). try:

Select Sum(Case when q3a = '' then 0 Else 1 end) as CountQ3a, Sum(Case when q3b = '' then 0 Else 1 end) as CountQ3b, Sum(Case when q3c = '' then 0 Else 1 end) as CountQ3c 
from YourTable

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[raniweb]

I just tried and I received an error:
The Query Designer does not support the CASE SQL construct.

Might there be another way? Thank you again!

 

[SQLSister]

Don't use query designer, use Query Analyzer. Query designer is useless for complex queries as it doesn't support all the real functionality of T-SQL.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[HogHunter]

From a design perspective, I would recommend that you store NULLs not blanks so the data can be ignored.

 

[raniweb]

That must be my problem, because I've never used it before. Should I have any precautions before I use it? Yes, I don't work much with SQL and if I do it's through the Query Designer. Thank you!

 

[HogHunter]

Both Query Designer and Query Analyzer offer you opprotunites to screw up. Unless you are issuing commands you do not know about you should be ok in Query Analyzer. Especially if you stick to SELECT statements. The benefit of using QA is that it forces you to write the statements and learn the language.

If you are looking for an easier to use environment that still let's you write SQL I would suggest you look at SQL IDE Pro. (I am not affliated with this product other than being a satified customer.)

I would recommend that you pick up a basic SQL book or at least refer to the books on line. A very popular bookaround my office is The Practical SQL Handbook: Using Structured Query Language by Judith S. Bowman, Sandra L. Emerson, Marcy Darnovsky ISBN: 0201447878

 

[raniweb]

Thank you very much for your help! I'll have to look into SQL a bit further if I keep getting asked to do things that I don't normally do with it. Thanks again!