CODEWITHSUNDEEP
phpmysql
XCS
XCS

Reputation: 28147

MySQL query and php edit

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:

(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...

Upvotes: 4

Views: 348

Answers (2)

Frank Farmer
Frank Farmer

Reputation: 39356

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,

Upvotes: 3

Marc B
Marc B

Reputation: 360692

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.

Upvotes: 3

Related Questions