Professional Web Applications Themes

needing a count field - MySQL

Hi, Consider if you will, the following: "select count(*) from Bookings where ContactID = x" The ContactID field is a foreign key from a 'Contacts' table. This is fine when I know an individual ContactID. When I get a set of ContactIDs thus: "select ContactID from Contacts where (etc..)" How would I get the 2nd query to also get a count as in the 1st? I've simplified these queries a bit so as to make my question clearer (?)..a 'Contact' might have made a booking and appear in the 'Bookings' table. I need to find certain contacts and get a ...

  1. #1

    Default needing a count field

    Hi,

    Consider if you will, the following:

    "select count(*) from Bookings where ContactID = x"

    The ContactID field is a foreign key from a 'Contacts' table. This is
    fine when I know an individual ContactID. When I get a set of
    ContactIDs thus:

    "select ContactID from Contacts where (etc..)"

    How would I get the 2nd query to also get a count as in the 1st? I've
    simplified these queries a bit so as to make my question clearer (?)..a
    'Contact' might have made a booking and appear in the 'Bookings' table.
    I need to find certain contacts and get a count of their bookings at
    the same time.

    Anyone?

    tia,
    Hughie

    hughie Guest

  2. #2

    Default Re: needing a count field

    On 15.12.2006 11:36, hughie wrote: 

    Use "GROUP BY ContactID" with "count(*)".

    robert
    Robert Guest

  3. #3

    Default Re: needing a count field

    > Use "GROUP BY ContactID" with "count(*)".

    thanks. for the record my query ended up as:

    select c.ContactID, c.ContactName, count(*) as "bookings"
    from Contacts c
    left join Members m on
    c.ContactID = m.ContactID
    join Bookings b on
    c.ContactID = b.ContactID
    where m.ContactID is null
    group by b.ContactID
    having bookings > 2
    order by bookings

    hughie Guest

Similar Threads

  1. incrementing field count. help please!!
    By Joe Science in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 18th, 11:57 PM
  2. How can I count field entries?
    By Fred in forum FileMaker
    Replies: 2
    Last Post: September 9th, 09:25 PM
  3. How to Count the Characters of a Field?
    By Frank Dulk in forum Microsoft Access
    Replies: 4
    Last Post: July 15th, 06:18 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139