select ABS (SUM) question

Posted on 2004-11-05
Last Modified: 2006-11-17
i have a survey that dumps the results to a mysql database....fome time to time we will have anywhere from 12 responses to over 700+. the survey choices range from 1-6 with six being highest (1-6 = very dissatisifed - very satisfied)

one of the survey metrics is "give me % for all those answers 3 (satisfied) or higher" which in mysql translates to:

SELECT ABS( SUM( Q1 >3  )  /21  )...etc.

as i have the query right now it works great...the problem lies in the fact, as stated earlier...the number of responses varies each time and the "/21" of the query SELECT ABS( SUM( Q1 >3  )  /21  ) will change often....without having to go and rewrite the query each there a way to use the COUNT feature in mysql to populate the "/XXX" part of the query?


full query
SELECT ABS( SUM( Q1 >3  )  /21  ) , ABS( SUM( Q2 >3  )  /21  ) , ABS( SUM( Q4 >3  )  /21  ) ,ABS( SUM( Q5 >3  )  /21  ),ABS( SUM( Q6 >3  )  /21  ),ABS( SUM( Q6 >3  )  /21  )  
FROM metrics
Question by:nriddock
    LVL 48

    Accepted Solution

    SELECT ABS( SUM( Q1 >3  )  / COUNT(Q1)  ) , ABS( SUM( Q2 >3  )  /COUNT(Q1)  ) , ABS( SUM( Q4 >3  )  /COUNT(Q1)  ) ,ABS( SUM( Q5 >3  )  /COUNT(Q1)  ),ABS( SUM( Q6 >3  )  /COUNT(Q1)  ),ABS( SUM( Q6 >3  )  /COUNT(Q1)  )  FROM metrics

    not work ??

    Author Comment

    perfect...thanks for the quick response

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now