Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Location
    Long Island
    Posts
    427

    SQL query question

    I have 2 tables called:

    1. referraltype
    2. leads

    the leads table has a referraltype_id field which links the referraltype table which also has referraltype_id field.

    referraltype_id
    referraltype_name

    I'm look to do a query to see how many times each referral comes up through out the leads table. If I have 7 referral types I want a query to return 7 records with the name of the referral and the number of times it shows up in the leads table

    ie.

    newpaper - 3
    cold calling - 5
    etc..


    I have been sitting here for 2 hours hours trying different queries and I can't figure it out.

    Thank you all for the input

    John

  2. #2
    Join Date
    Aug 2002
    Location
    Long Island
    Posts
    427
    MySQL and PHP sorry lol
    John Trovato
    In Office Networks, LLC
    Programmer, Cisco Network Engineer, Roofer, Biochemist, and Conductor.

  3. #3
    Join Date
    May 2002
    Location
    UK
    Posts
    2,994
    I would run two seperate queries where the second one is simply a recordcount where referraltype_id matches in the leads table.

    That is of course assuming that EACH lead is in a seperate row of the leads table. If they are simply incremented in the leads table on one row per type then you can simply join the tables.

    It's probably not the nicest way of doing it (in fact the best way would be to write a stored procedure... but you can't do that in the current version of mySQL).

    I think I have a way in a SQL statement I've used before... I'll have to trawl through source safe to have a look though.

  4. #4
    unless I'm mis-understanding the question ...

    select r.referaltype_name, count(*) from referaltype r, leads l
    where l.referaltype_id = r.referaltype_id
    group by refertaltype_name

  5. #5
    Join Date
    May 2002
    Location
    UK
    Posts
    2,994
    that was it 'count(*)'

    Damn my memory

  6. #6
    Join Date
    Aug 2002
    Location
    Long Island
    Posts
    427
    That's Prefect...


    thank you it saves so much time and coding
    John Trovato
    In Office Networks, LLC
    Programmer, Cisco Network Engineer, Roofer, Biochemist, and Conductor.

  7. #7
    Join Date
    Aug 2002
    Location
    Long Island
    Posts
    427
    is there a way to display all referraltypes even if there is no association to the leads table...

    Thank you

  8. #8
    yeah you'll need to turn in into an outer join

    select r.referaltype_name, count(*)
    from referaltype r
    left join lead l on l.referaltype_id=r.referaltype_id
    group by refertaltype_name

Posting Permissions

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