-2

I have haunted these forums always looking for tips on Joins and Sub_queries and have stockpiled solutions but I keep running into something that seems so simple but it eludes me.

The past few days of searching this forum haven't produced my answer.

So, here goes:

I have one table with a key ID, a person's Name, and a field called Status

I need to join this table to itself with the usual alias and simply present rows .

SO FAR I CAN GET either the Correct Count on 'Appointments' or the Correct Count on 'Fails' but my JOINS can't put it all together so that it looks complete as illustrated below:

id    | name                | Appointments (from Status values) | Fails (from Status values)
36   | Brad Jones       |         78                                            | 37
45   | Shirley Atwater |         56                                            | 44
47   | Jack Brill           |         33                                            | 60
53   | Norm Johnson  |         17                                            | 26

I can get one column of the id, one of the name, one from the Status value that is the Count of the Appointments Having 'Appointment' AS # Appointments or I can get the Count of the Status column rows where Status LIKE 'No Interest' but I can't get the Join

I use queries like this and many variations to get 3 columns out of 4 but when I move beyond to try Joins I lose my way.... so here I back up and just show a Query which can be used to either get the number of Appointments or by changing the status value can get the count of Fails....

SELECT trainerid, trainername, COUNT(*) AS Appointments
FROM trainer_appointments
GROUP BY status, trainerid
HAVING status = 'Appointment'
ORDER BY Appointments DESC

in this table each 'name' has a key id number so Brad Jones will always be id 36

I am counting how many rows marked with his id number fulfill the conditions of Status with value 'Appointment' and in a separate column counting Status values with 'No Interest' as FAILS

And I have also tried -- HAVING Status LIKE 'Appointment' or
WHERE Status LIKE 'Appointment' or
HAVING Status = 'Appointment'

When I go further with this query and try to JOIN this table under an alias for itself to get the FAILS COUNT WHERE "Status LIKE 'No Interest'" GROUP BY id, the Join flops.

The JOINS fail with a Variety of Reasons for the Errors out, or the query produces the wrong count.

I have tried adapting many other joins I have seen on this forum to this simple problem but I can't get the correct values even when the query does technically execute.

I would be thrilled if one of you Professionals would show me just how Easy this is!

Thunder
  • 3
  • 1
  • 1
    In your hauntings have you stumbled upon any questions that you thought were generally of a better quality than others? – Strawberry May 21 '15 at 01:12
  • 1
    what you dont need: a join. what you do need: to add some sample rows from your table to the question. – pala_ May 21 '15 at 01:37

1 Answers1

0

I'm going out on a limb and guess you don't need to join, you just want a count per name of a couple of different statuses. Conditional aggregation is what you need.

select name, 
       count(case when status = 'Appointment' then 1 end) appointments,
       count(case when status = 'Fail' then 1 end) fails
from <yourtable>
group by name

demo here.

Simply modify the conditional inside the case statements to adjust what value is being counted.

pala_
  • 8,901
  • 1
  • 15
  • 32
  • pala_, Thank you, thank you! I had never used cases before. But I have seen examples in these forums where the Self-Join was offered as a means of solving "similar" problems. – Thunder May 21 '15 at 06:47
  • @Thunder no problem. this is a pretty damn useful technique. if it solved your problem can you please mark it as accepted? – pala_ May 21 '15 at 06:51
  • Apparently I am too new to vote your answer up, Pala_, but yours is the clearest, most elegant solution I have seen and using a Case method which I can apply to lots of similar issues now! Thank you again for even going to the trouble of putting up the sqlfiddle demo! It is so difficult to ask a question here without someone jumping in to simply question the quality of the question itself. That part I despise about these forums, the question vultures. So, YOU took the time and effort to be kind, much appreciated, and I finally have a useful method to keep on using! – Thunder May 21 '15 at 06:53
  • @Thunder not vote up - just click on the tick mark under the vote toggles – pala_ May 21 '15 at 06:55
  • OKAY, thank you, I have taken another helpful tip from you, pala_. Answer accepted and another green check mark for Much Appreciated, if I could. – Thunder May 21 '15 at 06:55
  • GREAT! Thanks again for the Tip! YOU dah man, pala_! Saving me hours more of beating my head again this morning! – Thunder May 21 '15 at 06:56
  • Pala_, I have now marked you as the Go To Aussie,particularly after looking at techniques in your other answers that have helped people out. Genius! You alone have made joining this forum one of the best things I have done on the internet in a while. – Thunder May 21 '15 at 07:03