Multiple SQL count, join and where

[ukfreak]

Hi i have been trying to get the results for the code below and i can't seem toget myself around it. I need to get the results according to the myKad and Student Name on How many times the student has answered Excellent, Satisfaction and Poor for each question, each time they do the survey.

Table
_________

Student(id,name,mykad,...)
Customer Service(id, Q1,Q2,Q3)
Instructor(id, Q1,Q2,Q3)
Runner(id, Q1,Q2,Q3)

SELECT student.name, student.mykad,
COUNT(Q1) AS E,
COUNT(Q1) AS S,
COUNT(Q1) AS P
FROM customerservice
INNER JOIN student ON customerservice.id=student.id WHERE Q1='Excellent' AND yKad=student.myKad
INNER JOIN student ON customerservice.id=student.id WHERE Q1='Satisfaction' AND myKad=student.myKad
INNER JOIN student ON customerservice.id=student.id WHERE Q1='Poor' AND myKad=student.myKad
GROUP BY student.name, student.mykad

[Taywin]

You are saying that there are 4 tables total? But what I see is that you are using only 2 tables - student & customerservice. You mean you need to get the count of Q1 from each of those 3 tables???

[ukfreak]

You are saying that there are 4 tables total?
I'm just testing with one table first.

You mean you need to get the count of Q1 from each of those 3 tables???
Yes, i need to get the count for each question in each table.

[ukfreak]

updated:-

Table
_________

Student(id,name,mykad,...)
Customer Service(id, Q1,Q2,Q3,date)
Instructor(id, Q1,Q2,Q3,date)
Runner(id, Q1,Q2,Q3,date)

Result Should be:-

Name MyKad Q1E Q1S Q1P
___________________________________________________________

CHOW YEI CHUN 930712025013 2 3 1
CHUAH TZE AN 930712025013 4 4 0
MARDIANA BINTI ARIFFIN 930712025013 1 1 0

[Taywin]

What you may need is COUNT(DISTINCT expr,[expr...]). You can read about it here. This is MySQL format, but it should be very similar in SQL language.

Also, you 'join' table only once! Do not join table multiple times if you are joining the same tables. The WHERE clause doesn't look right to me because it should not be after the join clause. You may also look for 'having' clause if you use 'group by' as well.

[BitBlt]

Actually, you may wish to use correlated subqueries rather than inner joins. What happens if someone has no answers of a particular type? The row is not included in the result. Try this:

SELECT A.name, A.mykad, 
(select count(*) from customerservice B where B.id = A.id and B.Q1='Excellent') AS E, 
(select count(*) from customerservice C where C.id = A.id and C.Q1='Satisfaction') AS S,
(select count(*) from customerservice D where D.id = A.id and D.Q1='Poor') AS P
FROM student A

Ugly, I agree, and probably wouldn't perform well if there's a lot of data, but it will get you the result you're looking for.

[ukfreak]

Bitblt,

Thanks i'll try using this.

Student has to answer the question, cos only three options have been given,plus only 1 question appears at a time.

[ukfreak]

BitBlt,

I tried this code the result is ok, but i cant seem to group them by name and myKad, this is because each student does the survey atleast 5 times.... below is what i did...but den i can't seem to get the same result that i'm getting on sql to crystal report..

SELECT lecturer, name,myKad,
COUNT(CASE WHEN Q1='Excellent' THEN 3 ELSE NULL END) AS C1E ,
COUNT(CASE WHEN Q1='Satisfaction' THEN 2 ELSE NULL END) AS C1S,
COUNT(CASE WHEN Q1='Poor' THEN 1 ELSE NULL END) AS C1P
FROM customerservice cs INNER JOIN student st ON st.id = cs.id
WHERE (myKad=mykad)
AND (cs.q1 IN ('Excellent','Satisfaction','Poor'))
GROUP BY name,lecturer,myKad
ORDER BY lecturer

CHIN KOK SENG CHOO LE LENG 921204015930 2 0 0
CHIN KOK SENG MOHD MAHDHIR B MOHD YUSOF 900501015555 2 0 0

[ukfreak]

SELECT A.lecturer, A.name, A.mykad,
(select count(*) from customerservice B where B.id = A.id and B.Q1='Excellent') AS E,
(select count(*) from customerservice C where C.id = A.id and C.Q1='Satisfaction') AS S,
(select count(*) from customerservice D where D.id = A.id and D.Q1='Poor') AS P
FROM student A
Group by A.Name,lecturer,A.myKad,A.id
order by lecturer

CHIN KOK SENG MOHD MAHDHIR B MOHD YUSOF 900501015555 1 0 0
CHIN KOK SENG MOHD MAHDHIR B MOHD YUSOF 900501015555 1 0 0
CHIN KOK SENG CHOO LE LENG 921204015930 1 0 0
CHIN KOK SENG CHOO LE LENG 921204015930 1 0 0

[BitBlt]

Now I'm just confused about what you're trying to accomplish. Please post an explanation of the use case and what you expect your result to be.

[ukfreak]

Hi BitBlt,
Sorry for the confusion.

The database stores the student survey as Excellent or Satisfaction or Poor. So i have to calculate how many times the student has said Excellent or Satisfaction or poor for q1,q2,q3 for each category which is customer service, instructor and runner. Is the anything else you want me clarify here BitBlt? I can send the report if u want to have a look.

[BitBlt]

Received the report. I'm not really a Crystal Reports person, so I can't really help you there. As far as the query goes, the only thing I see that's missing is a way to differentiate the same person answering the questions on the same date (see page 1 of the report, lecturer Chin Kok Seng, student Foo Pei Pei.)

Also, you'll have to do the same pattern of correlated subselect for each category, so you'll have three per question for Customer Service, three per question for Instructor, three per question for Runner, like so:

SELECT A.name, A.mykad, 
(select count(*) from customerservice B1 where B1.id = A.id and B1.Q1='Excellent') AS Q1E1, 
(select count(*) from customerservice C1 where C1.id = A.id and C1.Q1='Satisfaction') AS Q1S1,
(select count(*) from customerservice D1 where D1.id = A.id and D1.Q1='Poor') AS Q1P1,
(select count(*) from customerservice E1 where E1.id = A.id and E1.Q2='Excellent') AS Q2E1, 
(select count(*) from customerservice F1 where F1.id = A.id and F1.Q2='Satisfaction') AS Q2S1,
(select count(*) from customerservice G1 where G1.id = A.id and G1.Q2='Poor') AS Q2P1,
(select count(*) from customerservice H1 where H1.id = A.id and H1.Q2='Excellent') AS Q3E1, 
(select count(*) from customerservice J1 where J1.id = A.id and J1.Q2='Satisfaction') AS Q3S1,
(select count(*) from customerservice K1 where K1.id = A.id and K1.Q2='Poor') AS Q3P1,
(select count(*) from instructor B2 where B2.id = A.id and B2.Q1='Excellent') AS Q1E2, 
(select count(*) from instructor C2 where C2.id = A.id and C2.Q1='Satisfaction') AS Q1S2,
(select count(*) from instructor D2 where D2.id = A.id and D2.Q1='Poor') AS Q1P2,
(select count(*) from instructor E2 where E2.id = A.id and E2.Q2='Excellent') AS Q2E2, 
(select count(*) from instructor F2 where F2.id = A.id and F2.Q2='Satisfaction') AS Q2S2,
(select count(*) from instructor G2 where G2.id = A.id and G2.Q2='Poor') AS Q2P2,
(select count(*) from instructor H2 where H2.id = A.id and H2.Q2='Excellent') AS Q3E2, 
(select count(*) from instructor J2 where J2.id = A.id and J2.Q2='Satisfaction') AS Q3S2,
(select count(*) from instructor K2 where K2.id = A.id and K2.Q2='Poor') AS Q3P2,
(select count(*) from runner B3 where B3.id = A.id and B3.Q1='Excellent') AS Q1E3, 
(select count(*) from runner C3 where C3.id = A.id and C3.Q1='Satisfaction') AS Q1S3,
(select count(*) from runner D3 where D3.id = A.id and D3.Q1='Poor') AS Q1P3,
(select count(*) from runner E3 where E3.id = A.id and E3.Q2='Excellent') AS Q2E3, 
(select count(*) from runner F3 where F3.id = A.id and F3.Q2='Satisfaction') AS Q2S3,
(select count(*) from runner G3 where G3.id = A.id and G3.Q2='Poor') AS Q2P3,
(select count(*) from runner H3 where H3.id = A.id and H3.Q2='Excellent') AS Q3E3, 
(select count(*) from runner J3 where J3.id = A.id and J3.Q2='Satisfaction') AS Q3S3,
(select count(*) from runner K3 where K3.id = A.id and K3.Q2='Poor') AS Q3P3
FROM student A

Told you it was ugly...

[ukfreak]

Hi, it doesnt matter if it's ugly, as long as it can show the result i think it's ok. Btw, how can i group by the name of the student?

[BitBlt]

Frankly, you'd be better off selecting the above mess into a temp table and doing any additional sorting/grouping from there.