By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,888 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

count and group by with OR

P: n/a
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. 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...

Please advice.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Not quite sure I follow.

Are you saying that if there are four rows with 3 in field1 and two with 3
in field2, you want to report 6? (In other words, you don't care which field
it comes from)

If so, try a Union query, and then do your counts on that query:

SELECT Field1 As TheField FROM MyTable
UNION ALL
SELECT Field2 FROM MyTable

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Abhi" <go****@megadelfi.com> wrote in message
news:1d**************************@posting.google.c om...
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. 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...

Please advice.

Nov 13 '05 #2

P: n/a
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:<eP********************@rogers.com>...
Not quite sure I follow.

Are you saying that if there are four rows with 3 in field1 and two with 3
in field2, you want to report 6? (In other words, you don't care which field
it comes from)
Yes, I don't care which field it comes from. It should indeed return 6
in this case, but if there are 10 rows and EACH row contains 3 in both
field1 and field2 then it should report 10, not 20. And of course I
need all the possible values counts reported.

If so, try a Union query, and then do your counts on that query:

SELECT Field1 As TheField FROM MyTable
UNION ALL
SELECT Field2 FROM MyTable


How to do counts on that query? I can only think of creating a
temporary table with results of this UNION query and then querying
that temp table. Is there any more elegant way?

P.S. Doesn't counting this UNION query return 20 and not 10 in a case
described above?

Abhi
Nov 13 '05 #3

P: n/a
"Abhi" <go****@megadelfi.com> wrote in message
news:1d**************************@posting.google.c om...
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:<eP********************@rogers.com>...
Not quite sure I follow.

Are you saying that if there are four rows with 3 in field1 and two with
3
in field2, you want to report 6? (In other words, you don't care which
field
it comes from)


Yes, I don't care which field it comes from. It should indeed return 6
in this case, but if there are 10 rows and EACH row contains 3 in both
field1 and field2 then it should report 10, not 20. And of course I
need all the possible values counts reported.

If so, try a Union query, and then do your counts on that query:

SELECT Field1 As TheField FROM MyTable
UNION ALL
SELECT Field2 FROM MyTable


How to do counts on that query? I can only think of creating a
temporary table with results of this UNION query and then querying
that temp table. Is there any more elegant way?

P.S. Doesn't counting this UNION query return 20 and not 10 in a case
described above?


Yes, you're correct that the union would return 20, not 10, in the case you
described. That's why we needed more information from you!

Okay, so you've got the following:

Id Field1 Field2
1 5 10
2 10 10
3 10 15
4 15 5

and you want to return

FieldValue Count
5 2
10 3
15 2

Correct?

Create a query that returns all of the unique values in the table and name
it qryUniqueValues:

SELECT Field1 AS LookupField
FROM MyTable
UNION
SELECT Field2 AS LookupField
FROM MyTable

Create a 2nd query that joins qryUniqueValues to your table and name it
qryIntermediate

SELECT MyTable.Id, MyTable.Field1, MyTable.Field2,
qryNewsgroupQuestionUniqueValues.LookupField
FROM MyTable
INNER JOIN qryValues
ON (MyTable.Field1 = qryUniqueValues.LookupField)
OR (MyTable.Field2 = qryUniqueValues.LookupField)

Create a 3rd query base that figures out the counts using qryIntermediate:

SELECT LookupField, Count(Id) As Total
FROM qryIntermediate
GROUP BY LookupField

Now, assuming you're using Access 2000 or newer, it's possible to combine
all of that into a single query, but I wanted to explain the steps:

SELECT MyData.LookupField, Count(MyData.Id) AS Total
FROM [SELECT UV.LookupField, MyTable.Id
FROM MyTable
INNER JOIN
(SELECT Field1 AS LookupField FROM MyTable UNION SELECT Field2 FROM MyTable)
AS UV
ON (MyTable.Field1 = UV.LookupField) OR (MyTable.Field2 = UV.LookupField)].
AS MyData
GROUP BY MyData.LookupField;
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.