MS Access SQL query counting values in more than one column

Question

I'm having an issue that basically invovles an SQL query in MS Access counting criteria from multiple fields. The data I am working with is as follows:-

Option 1     Option 2    Option 3
abc          def          ghi
abc          ghi          def
def          abc          jkl

And how I want the query to display my data:

abc - 3
def - 3
ghi - 2
jkl - 1

Forgive me if this is quite a basic question. I didn't know how best to put into words the nature of my enquiry and I am relatively new to SQL.

Thanks.

Yeah the data itself is in a horrible format, it's the car order configuration data history from a manufacturer that we have to do some analysis on. Hansup - if the next row contains another instance of abc then the count should be 4 yes, if however abc was not present in row 4 then the count should be 3. I am attempting to count each instance of abc when it appears anywhere in a row essentially. — Monty
– Monty, Commented Jan 25, 2016 at 15:08
Hansup - Each time a option code (such as abc) appears in a record it can only occur once in that record as it is a unique identifier for one of the car's options. — Monty
– Monty, Commented Jan 25, 2016 at 15:15

sagi · Accepted Answer · 2016-01-25 14:56:56Z

1

SELECT OPTION,sum(count) from (
  SELECT OPTION1 as OPTION,COUNT(*) as count FROM Table GROUP BY OPTION1
  union all
  SELECT OPTION2,COUNT(*) as count FROM Table GROUP BY OPTION2
  union all
  SELECT OPTION3,COUNT(*) as count FROM Table GROUP BY OPTION3
)group by OPTION

This should do the trick, not sure if that is the correct syntax but just adjust it.

Its a two steps query, first - count for each column grouping by their name. And second - sum the total unioning all the results

answered Jan 25, 2016 at 14:56

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Monty Over a year ago

Thanks Sagi, this essentially allowed me to do what I wanted :)

Collectives™ on Stack Overflow

MS Access SQL query counting values in more than one column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related