CODEWITHSUNDEEP
phpmysql
TerryMatula
TerryMatula

Reputation: 1264

Count total results of a number within a certain range using MySQL and PHP

The goal of the project is to check the total character count of a textarea field and compare the conversion rate against historical data. Simply: Person X enters 300 characters, and I want to see the conversion rate of previous people who entered between 260 and 340 characters.

Here's a working MySQL query: 

SELECT 
  COUNT(*) AS total_count, 
  SUM(converted) AS total_converted, 
  (SUM(converted) / count(*)) * 100 AS conversion_rate, text_length
FROM my_table WHERE text_length IS NOT NULL
GROUP BY text_length div 40

This works fine for lower numbers, but at a certain point (like over 400 characters), I'd like to just lump them all together. Is there a way to check if the 'text_length' is over a certain value and then just COUNT everything above it?

Also, if anyone has any suggestions for a better overall approach, I'd be happy to try that, too. Thanks!

Upvotes: 2

Views: 167

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53870

Using UNION, you could do this:

(SELECT 
  COUNT(*) AS total_count, 
  SUM(converted) AS total_converted, 
  (SUM(converted) / count(*)) * 100 AS conversion_rate, text_length
FROM my_table
WHERE text_length <= 400
GROUP BY text_length div 40)
UNION
(SELECT 
  COUNT(*) AS total_count, 
  SUM(converted) AS total_converted, 
  (SUM(converted) / count(*)) * 100 AS conversion_rate, 401 AS text_length
FROM my_table
WHERE text_length > 400)

Notice that I've also removed the WHERE text_length IS NOT NULL which is no longer needed. Your text_lengths that are greater than 400 will be grouped as text_length 401.

Upvotes: 1

Marc B
Marc B

Reputation: 360762

How about

GROUP BY (text_length > 400), CAST((text_length / 40) AS INTEGER)

That would make anything with 401+ characters group together into a single group, and then anything 400 or less get grouped by the 40 char divisions.

Upvotes: 0

Related Questions