sql query problem

[Sid3335]

I have a table that holds customer feedback data.

theres are 4 fields (questions) and in each of these fields is an integer value (answer)

Answers
1 = excellent
2 = good
3 = average
4 = below average
5 = poor

what i would like is a query that can give me an array of results so that i know how many people voted for each question, but by answer also, so should return:

[question1] = array(
[excellent] = 5
[good] = 3
[average] = 7
[below average] = 4
[poor] = 7
)
[question2] = array(
[excellent] = 3
[good] = 1
[average] = 4
[below average] = 8
[poor] = 7
)

etc, so far i have:

SELECT
Count(`customer_feedback`.`booking_process`),
Count(`customer_feedback`.`venue_facilities`),
Count(`customer_feedback`.`venue_staff`),
Count(`customer_feedback`.`enjoyment`)
FROM
`customer_feedback`
WHERE
`customer_feedback`.`agent_id` = '115'


but obviously that just gives me the number of people who have voted for each question, not an array of count(answers) for each question.

Any help is appreciated

[chazzy]

you can use a case statement to break it up by value.

Edit: or maybe i'm a bit confused. do you want to know the total value of each question? the total value of the votes per response?

[CarolineBogart]

Instead of count use sum

[Sid3335]

Sum will not do it.

i want to return an array of each answer (1-5) for each question.

so for each question it will tell me how many people answered:
excellent
good
etc

i will try the case statement (of which i was unaware) once i'm back at work. Thanks chazzy

[mudelta]

hello, can you give me the structure of that table?

[NightShift58]

It's a bit long-winded but I think this will give you the results you want: SELECT 'question1' as `question`,
sum(IF(5 = `q1`.`booking_process`,1,0)) as `excellent`,
sum(IF(4 = `q1`.`booking_process`,1,0)) as `good`,
sum(IF(3 = `q1`.`booking_process`,1,0)) as `average`,
sum(IF(2 = `q1`.`booking_process`,1,0)) as `below_avg`,
sum(IF(1 = `q1`.`booking_process`,1,0)) as `poor`
FROM `customer_feedback` `q1`
WHERE `customer_feedback`.`agent_id` = '115'
UNION
SELECT 'question2' as `question`,
sum(IF(5 = `q2`.`venue_facilities`,1,0)) as `excellent`,
sum(IF(4 = `q2`.`venue_facilities`,1,0)) as `good`,
sum(IF(3 = `q2`.`venue_facilities`,1,0)) as `average`,
sum(IF(2 = `q2`.`venue_facilities`,1,0)) as `below_avg`,
sum(IF(1 = `q2`.`venue_facilities`,1,0)) as `poor`
FROM `customer_feedback` `q2`
WHERE `customer_feedback`.`agent_id` = '115'
UNION
SELECT 'question3' as `question`,
sum(IF(5 = `q3`.`venue_staff`,1,0)) as `excellent`,
sum(IF(4 = `q3`.`venue_staff`,1,0)) as `good`,
sum(IF(3 = `q3`.`venue_staff`,1,0)) as `average`,
sum(IF(2 = `q3`.`venue_staff`,1,0)) as `below_avg`,
sum(IF(1 = `q3`.`venue_staff`,1,0)) as `poor`
FROM `customer_feedback` `q3`
WHERE `customer_feedback`.`agent_id` = '115'
UNION
SELECT 'question4' as `question`,
sum(IF(5 = `q4`.`enjoyment`,1,0)) as `excellent`,
sum(IF(4 = `q4`.`enjoyment`,1,0)) as `good`,
sum(IF(3 = `q4`.`enjoyment`,1,0)) as `average`,
sum(IF(2 = `q4`.`enjoyment`,1,0)) as `below_avg`,
sum(IF(1 = `q4`.`enjoyment`,1,0)) as `poor`
FROM `customer_feedback` `q4`
WHERE `customer_feedback`.`agent_id` = '115'

[Sid3335]

thanks that last example works great. thanks.

if theres a shorter/better way to acheive the same result i'd like to know about it.

i could not acheive the same results using a case statment.

[mudelta]

that seems a nasty query to me for such a simple problem.

i doubt if it can't be done much easier, but therefore i want to know the structure of your tables.

[edit]

i think i understand the structure:

you have a table :customer_feedback

that table has at least 5 columns:

agent_id
booking_process
venue_facilities
venue_staff
enjoyment

is that correct?

this structure is the reason why you have to make such a difficult query.

if the structure was something like this:

tablename:VOTES

columns:

VoteId
AgentId
QuestionId
Result

you could have what you want with the following query:

SELECT QuestionId, Result, COUNT( Result ) as CountResult
FROM VOTES
GROUP BY QuestionId, Result


the resultset will be in the following form:

QuestionId_________Result__________CountResult
___1 _______________ 1 _______________ 5
___1 _______________ 2 _______________ 3
___1 _______________ 3 _______________ 10
___1 _______________ 4 _______________ 8
___1 _______________ 5 _______________ 20
___2 _______________ 1 _______________ 4
___2 _______________ 2 _______________ 4
___2 _______________ 3 _______________ 6
___2 _______________ 4 _______________ 14
___2 _______________ 5 _______________ 7

etcetera


[edit]

[Sid3335]

CREATE TABLE `customer_feedback` (
`id` int(10) unsigned NOT NULL auto_increment,
`agent_id` int(10) default NULL,
`booking_reference` int(10) default NULL,
`booking_process` tinyint(1) default NULL,
`venue_facilities` tinyint(1) default NULL,
`venue_staff` tinyint(1) default NULL,
`enjoyment` tinyint(1) default NULL,
`notes` blob,
`date_time` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[mudelta]

as i expected.

See my comment on my previous post.

the datamodel you are using isn't the best solution

Is it possible to adapt it?

[chazzy]

as i expected.

See my comment on my previous post.

the datamodel you are using isn't the best solution

Is it possible to adapt it?

It's only not the best structure for one query. You don't know what other queries are running on this structure where it is optimized for this. Also, adding this logic as a stored procedure, w/ its case structure might work best if your version supports it.

[NightShift58]

I have to second Chazzy's comments... As a rule, one doesn't create tables to fit the needs of queries but queries to retrieve data from existing tables. I think that if it were that easy, we would all use flat files.

As to my SELECT, I realize that it's a "bit" on the long side... But I tried with both CASE and FIELD and, in the end, IF was the better option.

FIELD would have worked, but it's syntax is longer than IF and the results I needed were simply 1 or 0, perfect for an IF.

CASE is nice but it is no panacea. In this case it doesn't help because we're not checking 1 value out of many in the traditional horizontal manner. We're looking for a particular value in a particular field, one record at a time. CASE would only work if we had 5 answer fields for each question, a total of 20 answer fields per record. Then - and only then - could we CASE through each record. CASE works horizontally, so to say... Of course, if I can use IF then I can also use CASE, but, in this example, it would only be to simulate an IF.

I think this statement could be used as a basis for stored procedure or a view. Obviously, that would depend on the database software, version, etc.

I personally don't know how else to query a horizontal data source and return it in a conditional vertical form with previously non-existent fields.

If there's a better way, please post: I need it.

[mudelta]

i'm sorry,

i was too quick to state that the used datamodel isn't the best solution.

I don't know which datamodel they use.