how do I count from multiple fields?

[opticalman]

I want a count of all the different services done. This works OK.

SELECT service1 as serv1 , COUNT( service1 ) as howmany
FROM MyDatabase
GROUP BY service1
ORDER BY service1

But each record has a place for 6 services to be entered, I.E. service1, sevvice2, ….. service6. ( I humbly ask that you not remind why that is a poor table design ) How can I get the same count of all services from all 6 fields?

Thanks in advance

Jim Rumbaugh

 

[SQLBill]

Can you post the table schema and sample data for us? That would probably let us give you the best answer.

-SQLBill

Posting advice: FAQ481-4875

 

[r937]

SELECT service 
     , COUNT( service ) as howmany
  FROM (
       SELECT service1 as service 
                       FROM MyDatabase 
       UNION ALL
       SELECT service2 FROM MyDatabase 
       UNION ALL
       SELECT service3 FROM MyDatabase 
       UNION ALL
       SELECT service4 FROM MyDatabase 
       UNION ALL
       SELECT service5 FROM MyDatabase 
       UNION ALL
       SELECT service6 FROM MyDatabase 
       ) as d
GROUP BY service

r937.com | rudy.ca

 

[opticalman]

SQLBill

Thank you for the suggestion. I will try to give enough info with future posts.

R937

That did it. Thank you.

From your code I now see how to do what I call a 'compound select'. I do not know what the technical name for it is. But can I see you did a select on the results of a group of selects that were added together. Is there a correct name for what I have called a 'compound select'? The only question I have is: what is the " as D ". I assume... D is the temporary name of the table that is created by the joins. It could of been written as: ' as AllServices ' or some other descriptive name. Is my assumption correct?

I consider my problem solved.

Thank you both

Jim Rumbaugh

 

[r937]

thanks, jim

i think it's called a nested select

the inner one is called a subquery, and it produces what is called a derived table

yes, the table alias "d" could be any name at all

r937.com | rudy.ca