We help IT Professionals succeed at work.

how can i count values between min and max out from a table?

Medium Priority
304 Views
Last Modified: 2013-12-17
hello experts,
i have the following table:
table_prod
id : integer
date: timestamp
weight: integer.
paid : boolean.

i need a query that will give me an answer as following :
1-19 | 20-29| 30-39|
25 | 57 | 74|


here i can read that records containing between 1-19 appear 25 times in my table,
the next array between 20 and 29 appear 57 times....etc.

question:
what would be the query?
thank in advance
Comment
Watch Question

Author

Commented:
my starting point is as follow,
i need an answer for diffrerent values :


SELECT count(*)
FROM trash_prod
WHERE weight BETWEEN 2  AND 19;

Commented:

SELECT 
  case when weight between 0 and 19 then '0-19' 
  case when weight between 20 and 29 then '20-29' 
  case when weight between 30 and 39 then '30-39' 
  else 'other' 
  end as weightRange, count(*)  
FROM trash_prod
GROUP BY 1
 

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
if i try it, i receive the following error
------------------------------------------------------------

ERROR:  syntax error at or near "case"
LINE 7:   case when weight between 21 and 40 then '20-29'
          ^

********** Error **********

ERROR: syntax error at or near "case"
SQL state: 42601
Character: 147



Author

Commented:
hooo,
i found the error , i only need one case ;o)
thanks a lot

Commented:
Oh sorry. Copy and paste error. Yes only one CASE.
http://www.postgresql.org/docs/7.1/static/functions-conditional.html
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.