1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

What is wrong with this query?

Discussion in 'MySQL' started by Richb201, Oct 15, 2020.

  1. #1
    I have a table called survey_results. It has fields called item and also user_email. I have another table called business_components and it has fields called bus_comp and qualified. For each item in survey_results I want to count the number of rows in business_components where the business_components.bus_comp=survey_result.item AND business_components.qualified="yes". Here is what I came up with:
    SEMrush
    SELECT sr.user_email,COUNT(bc.bus_comp) FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp AND bc.qualified='yes'
    GROUP BY sr.user_email

    This seems to work and it yields this

    sr.usr COUNT(bc.bus_comp)
    2
    1

    I'd like to add another column to the result. I'd like to Count the total number of sr.item=bc.bus_comp without the bc.qualified, for each user. How can I do this?
     
    Richb201, Oct 15, 2020 IP
    SEMrush
  2. JEET

    JEET Notable Member

    Messages:
    3,492
    Likes Received:
    423
    Best Answers:
    16
    Trophy Points:
    235
    #2
    I'd suggest you use a separate query for that, because even if you run that as sub query inside this one, still it will be load of 2 queries and tables will need to be rescanned.
    Your "where" clause is different for this selection, so same query will not work. Tables will be rescanned with a sub query also.
    So why complicate a simple query. Run a different one.
     
    JEET, Oct 15, 2020 IP
  3. Richb201

    Richb201 Greenhorn

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    Well, I managed to get two more columns by using:
    SELECT sr.user_email,COUNT(bc.qualified="yes") FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp GROUP BY sr.user_email
    SELECT sr.user_email,COUNT(bc.qualified<>"yes") FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp GROUP BY sr.user_email

    This makes no sense. Is it not possible to have a qualification inside a count?
     
    Richb201, Oct 15, 2020 IP
  4. Richb201

    Richb201 Greenhorn

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #4
    Thank you for the response. I have thought about that. Here is what I'd like to do:
    SELECT sr.user_email,COUNT(bc.qualified="yes") FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp GROUP BY sr.user_email
    SELECT sr.user_email,COUNT(bc.qualified) FROM survey_results sr, business_components bc WHERE sr.item=bc.bus_comp GROUP BY sr.user_email

    But, how do I bring the results together? Should I create a TEMP table with the ratio of qualified/total? I also have a need to do some additional calcs such as multiplying each resulting row by a wage. Thanks again.
     
    Richb201, Oct 15, 2020 IP
  5. JEET

    JEET Notable Member

    Messages:
    3,492
    Likes Received:
    423
    Best Answers:
    16
    Trophy Points:
    235
    #5
    In both these queries your WHERE clause is same, so you can try this:


    SELECT sr.user_email,
    COUNT(bc.qualified = 'yes') as qYes,
    COUNT(bc.qualified) as qAny
    FROM survey_results sr, business_components bc
    WHERE sr.item=bc.bus_comp
    GROUP BY sr.user_email

    or this,

    SELECT sr.user_email,
    SUM(if(bc.qualified = 'yes', 1, 0)) as qYes,
    COUNT(bc.qualified) as qAny
    FROM survey_results sr, business_components bc
    WHERE sr.item=bc.bus_comp
    GROUP BY sr.user_email
     
    JEET, Oct 15, 2020 IP