MySQL query and php edit

Question

I have many database entries, each containing a value.

I want to show some statistics regarding those values, so I should be able to say:

how many entries (rows) have the value between 0 and 5,
how many entries (rows) have the value between 6 and 10,
how many entries (rows) have the value between 11 and 20,
how many entries (rows) have the value more then 20.

(the values above where given only as an example)

Let's say I'll have like 6-7 intervals that I want to group the values by. Can I do this in a single MySQL query? If yes, how can I acces the grouped values using ~~photoshop~~ PHP aftewards?

Thanks!

EDIT:
Seems like the query is actually harder. I have 2 columns (val1 and val2). I want the query to count how many items have val1/val2 (val1 divided by val2) between an interval...

OMG, I think I should take a break... :)) I meant, php lol.

XCS
– XCS

2011-05-12 19:09:30 +00:00
Commented May 12, 2011 at 19:09 — XCS
– XCS, Commented May 12, 2011 at 19:09

Frank Farmer · Accepted Answer · 2011-05-12 19:32:14Z

3

SELECT 
   SUM(col BETWEEN 0 AND 5) AS 0_to_5,
   SUM(col BETWEEN 6 AND 10) AS 6_to_10,

etc. etc.

re: your edit:

  SELECT 
   SUM(val1/val2 BETWEEN 0 AND 5) AS 0_to_5,
   SUM(val1/val2 BETWEEN 6 AND 10) AS 6_to_10,

edited May 12, 2011 at 19:32

answered May 12, 2011 at 19:15

Frank Farmer

39.6k12 gold badges73 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

webbiedave Over a year ago

+1 From there it's just a simple step of grabbing a screenshot of the outputted values and pasting them into photoshop.

webbiedave Over a year ago

@Cristy: I know. I was joking :)

XCS Over a year ago

I know you were joking, I was talking to Frank; I've added something to the query :))

Marc B · Accepted Answer · 2011-05-12 19:20:04Z

3

If the ranges you're splitting the counts up into are of the same size, you can use the modulo operator:

SELECT MOD(col, 5), COUNT(col)
FROM ...
WHERE ...
GROUP BY MOD(col, 5)

which would group everything into a 5-per-category type thing automatically, and handle an unlimited range of categories.

If the ranges are different sizes, then you'll have to generate a large case/if-then-else type construct to check for each range individually.

answered May 12, 2011 at 19:20

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

MySQL query and php edit

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related