Results 1 to 7 of 7

Thread: Counting items

  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23

    Question Unanswered: Counting items

    Hi,

    I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

    SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

    This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?

    Cheers.

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi!

    Would this one help out?

    Code:
    SELECT	  appt.ref
    	, (Case When noteCount > 0 Then 1 Else 0 End) AS notes
    FROM	  touchAppointments appt
    	, (SELECT appointment
    		, COUNT(*) as noteCount 
    	   FROM touchNotes) note
    WHERE	appt.practitioner=1
    AND	appt.ref = note.appointment
    Greetings,
    Carsten

  3. #3
    Join Date
    Mar 2004
    Posts
    80
    I would have writen like this
    <code>
    SELECT appt.ref , count(*)/count(*) AS notes
    FROM touchAppointments as appt inner join touchNotes as note
    on appt.ref = note.appointment
    WHERE appt.practitioner=1 group by appt.ref
    </code>

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You would?

    count(*)/count(*) is at best going to return only 1s or Nulls, and at worst would return DivZero errors.

    There are serveral ways to do this. CarstenK had one, though it is preferable to use a JOIN rather than linking tables in the WHERE clause.

    Here are two more methods:

    SELECT touchAppointments.ref, cast(count(touchNotes.appointment) as bit) notes
    FROM touchAppointments
    left outer join touchNotes on touchNotes.appointment = touchAppointments.ref
    WHERE touchApointment.practictioner = 1
    GROUP BY touchAppointments.ref

    SELECT touchAppointments.ref, isnull(notesSubquery.hasnotes, 0) as notes
    FROM touchAppointments
    left outer join (select distinct touchNotes.appointment, 1 as hasnotes from touchNotes) notesSubquery
    on notesSubquery.appointment = touchAppointments.ref
    WHERE touchApointment.practictioner = 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    80
    you are right
    count(*)/count(*) is at best going to return only 1s
    but how come nulls and div by zero error(even at worst case) with inner join on touchAppointments.ref.

  6. #6
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    How do you get the "0" paulbrooks wants to get with his CASE (.....)?


    Carsten

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23

    Talking

    Blindman,

    Your second solution worked the trick. It returns a 1 for true and 0 for false, which is exactly what I needed.

    Thanks a lot, guys.


    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •