June 10, 2008 at 8:30 am
Hi
I've got a database which has 11 tables.
One table has details such as service name, location, date.
The other tables have fieldnames which are question numbers:
So the first of these tables has 17 fields, questions 1, 1_1, 1_2...
The user fills in a form for each of these questions and answers to each of them can be one of the following:
Yes, No, Partial, N/A, or None.
I want to create a results page where i can select the location and service name and date to and from which i've done.
That page must then display all results with those conditions, but also, it has to summarise each question in %.
So the results page should looks something like this:
Location Service Name Date range
___________________________________________________
Yes No Partial N/A None
Q1 40% 20% 20% 10% 10%
Q2...........................................................................................
Q3...........................................................................................
How do i select all the results from a table column and count how many Yes's, No's, NA's, Partial's and None's there are?
Hope this makes sense.
June 10, 2008 at 11:46 am
Well, you could do something like:
select
(select count(*)
from dbo.Table1
where [1] = 'Yes') as Yes,
(select count(*)
from dbo.Table1
where [1] = 'No') as No,
... and so on
But you really might do better to have pivot operations like this done by an application designed for it. Excel is great at doing pivot tables. Reporting Services does them quite nicely too. Either allows you to do things that a static query can't, like on-the-fly filters and sorts, changing from percent results to total with just a couple of mouse-clicks, and so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 13, 2008 at 4:55 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply