CODEWITHSUNDEEP
sqlsql-servert-sql
BlueTree
BlueTree

Reputation: 111

How to retrieve the different range of values from database using SQL?

I am currently trying to retrieve light values and sort them into a pie chart using an ajax pie chart tool. I really need help for the SQL statement for retrieving and counting the different set of values.

Here is the 3 sets of values i want to retrieve:

1) retrieve and count lightValues that are < 24

2) retrieve and count lightValues that are between 24 and 30

3) retrieve and count lightValues that are > 30

The pie chart is the result i want to achieve. Currently I am using a raw data to display what i want to achieve.

Image for pie chart:

enter image description here

Image for database:

enter image description here

Upvotes: 0

Views: 91

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You could use UNION ALL:

SELECT 'less_than_24' AS group_name, COUNT(*) AS counter
FROM table_name
WHERE lightValue < 24
UNION ALL
SELECT 'from_24_to_30', COUNT(*) AS counter
FROM table_name
WHERE lightValue BETWEEN 24 AND 30
UNION ALL
SELECT 'more_than_30', COUNT(*) AS counter
FROM table_name
WHERE lightValue > 30;

LiveDemo

Output:

╔═══════════════╦═════════╗
║  group_name   ║ counter ║
╠═══════════════╬═════════╣
║ less_than_24  ║       4 ║
║ from_24_to_30 ║       2 ║
║ more_than_30  ║       2 ║
╚═══════════════╩═════════╝

Upvotes: 3

Alex K.
Alex K.

Reputation: 175766

One way

select
    count(case when lightvalue < 24 then 1 end),
    count(case when lightvalue between 24 and 30 then 1 end),
    count(case when lightvalue > 30 then 1 end)
from tbl

Upvotes: 6

Related Questions