count with group by with OR

[Abhi]

Hi!

I am wondering if this query is possible somehow:

I have a table with many fields that all can have a value from 1 to 5. if I
wanna see the count of each value from ONE field, then this is easy:
SELECT field1, count(field1) as cntnr FROM table group by field1

But the thing is that I need to see the count of each possible value (still
1 to 5), but two or more fileds are to be considered. How is this done? Is it
possible at all?
I am sorry if this is tooooooo stupid question, but I haven't found the
solution during all the day.

Or in other words, I need to know how many times all possible values are
present in field1 OR field2. I don't care which field it comes from. And if
one value is present in one row in both field1 and field2 then it should be
counted only once. Like 'OR' you know...

if there are 10 rows and EACH row contains value 3 in both field1 and field2
then it should report 10, not 20.


It is easy to do with multiple queries like this:

SELECT count(id) WHERE field1 = 1 OR field2 = 1
SELECT count(id) WHERE field1 = 2 OR field2 = 2
SELECT count(id) WHERE field1 = 3 OR field2 = 3

But this is much slower. Is there more elegant way of doing this?

Please advice.

 

[Jeff Boyce]

Abhi

(remember, you asked about a "more elegant way..." <g>)

I strongly suspect that you have repeating fields (closely related subject
matter, with the field names containing data), perhaps in response to a
survey or test. This is one scenario in which multiple fields would have
potential values from 1 to 5. If so, your "table" is, in fact, a
"spreadsheet", and not a relational table.

The "elegant" solution, if this is the case, is to modify your data
structure to have only a single column with the "score", and another column
with the "question". As you have found, Access can easily handle aggregate
operations on a single column. This is because these functions in Access
are predicated on a well-normalized design.

Duane H. has an example of a survey/test database you could consider as a
template if my suspicions are accurate -- see:

Duane Hookom has a sample survey database at
http://rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[John Spencer (MVP)]

Not elegant and values are hard coded, but

SELECT Sum(IIF(Field1=1 Or Field2=1,1,0)) as CountOnes,
Sum(IIF(Field1=2 Or Field2=2,1,0)) as CountTwos,
Sum(IIF(Field1=3 Or Field2=3,1,0)) as CountThrees,
Sum(IIF(Field1=4 Or Field2=4,1,0)) as CountFours,
Sum(IIF(Field1=5 Or Field2=5,1,0)) as CountFives
FROM YourTable

 

[Abhi]

Abhi

(remember, you asked about a "more elegant way..." <g>)

I strongly suspect that you have repeating fields (closely related subject
matter, with the field names containing data), perhaps in response to a
survey or test. This is one scenario in which multiple fields would have
potential values from 1 to 5. If so, your "table" is, in fact, a
"spreadsheet", and not a relational table.

no, this is not a survey.

The "elegant" solution, if this is the case, is to modify your data
structure to have only a single column with the "score", and another column
with the "question". As you have found, Access can easily handle aggregate
operations on a single column. This is because these functions in Access
are predicated on a well-normalized design.

Well, maybe. Actually I am experimenting with the data created with other
program. So the database design is not mine. Maybe it is indeed the best way
to have only single column in this case. But I need sometimes results with
one column, sometimes from others, sometimes from two or more columns
together and sometimes from two or more colums with OR.

If I design a new table with single column for score does it affect the
speed? I mean right now it is very quick and easy to get the results from one
column

SELECT field1, count(field1) as cntnr FROM table group by field1

it is also easy to get results from multiple columns with 'AND' like this

SELECT field1, count(field1) as cntnr FROM table WHERE field1=field2 group
by field1

But with 'OR' this kind of query doesn't work.

So what about speed if I redesign the data to have a single column for
score? Then I need one column more to describe which kind of score it is and
I should always define the value of that column in a query. How does this
affect the speed?

 

[Jeff Boyce]

Abhi

I've not done a speed comparison test, so you might post back here if you do
so -- other 'group readers may be interested.

Proper indexing, though, should keep it moving fast.

John's response points out a way to use the existing data structure.

 

[Abhi]

This is very interesting way of doing this. It returns results in one row,
but who knows when I will need that option. Very interesting!

Abhi

 

[Abhi]

OK. Let's see. I just thought that maybe your or someone else knows at least
theoretically which kind of tables are quicker. The ones with many columns
and less rows, or those which have all the values in one column and have much
more rows therefore.

Abhi

 

[Abhi]

It seems that you are right. I should redesign the table. But how the query
with AND is done if table looks like this?

id, type, values

With OR it is easy:
SELECT values, count(values) FROM table WHERE type = 1 OR type = 2

But I need to count how many times every possible value (1-5) is present
ONLY in cases when the value is the same in the type = 1 AND type = 2

Abhi

 

[Jeff Boyce]

Abhi

I am still unclear if the underlying situation you are modeling is
one-to-many or not. When you say

id, type, values

I interpret this to mean that your "values" field could hold more than one
value. This is not well-normalized, if true. John provided a mechanism if
you keep your current structure -- do you need to change?

Can you provide an example of actual data that would go in these rows?

 

[Abhi]

Yes, "values" field holds more than one value. And yes, I have to change
current table structure because ultimately I will not know how many "fields"
I will have. So I can't create a table with many columns simply because
coulmns number is not known and it is actually increasing every once.

As I answered already before that it is not survey, but yes, it is very
similar to survey, it is some kind of statistics table. Let's say it IS
survey, then it easier to understand. In this case I have the following table:

id, questions, answers
1,q1,1
2,q1,5
1,q3,2
3,q1,3
4,q1,5
1,q2,2
2,q3,1
2,q2,1
etc

So, there is undetermined number of questions and each of them can have
answer 1 - 5. If I want to count all the possible answers to q1 then it is
easy:
SELECT answers, count(answers) FROM table WHERE questions = q1 GROUP BY
answers

Sometimes I need to count together all the possible answers to q1 OR q2
which is also easy:

SELECT answers, count(answers) FROM table WHERE questions = q1 OR questions
= q2 GROUP BY answers

BUT I have absolutely no idea how the query should look if I need to count
all the possible answers in cases where answers to q1 AND q2 are the same.

Or in other words, if it would be a survey, I would need to to count all the
persons who answered 1 to BOTH q1 AND q2, then I would need to count all the
persons who answered 2 to bOTH q1 AND q2 and so on...

Please advice.

Abhi

 

[Jeff Boyce]

Abhi

Thank you for further explanation. I will repeat my first suggestion --
take a look at Duane H.'s AtYourSurvey as a model for how items with
multiple potential "answers" can be constructed. Although your situation is
not a survey, the techniques Duane used will, I believe, be helpful.

Jeff Boyce
<Access MVP>