make range in sql

Question

Hello i have a question anyone can help

i have this table that contains the receipt number and amount

and i want to sql query to make a report with a range of receipt net amount

my source
amount receipt number
50 re01
200 re02
12 re03
13 re04

I want this table below

range Amount receipt count

0- 50 10000 50
50-100 20000 10

Answer 1

Hi @ali mohammed
How about this query using 'case when':

;WITH CTE AS  
(  
 SELECT CASE WHEN Amount >= 0 AND Amount < 50 THEN ' 0-50'  
             WHEN Amount >= 50 AND Amount < 100 THEN '50-100'  
             WHEN Amount >= 100 AND Amount < 150 THEN '100-150'   
	         WHEN Amount >= 150 AND Amount < 200 THEN '150-200'   
	         WHEN Amount >= 200 AND Amount < 250 THEN '200-250'   
	         ELSE '250-MAX' END AS Range,Amount  
 FROM #TEST  
)  
SELECT Range as [score range],SUM(Amount) AS Amount,COUNT(*) AS [receipt count]  
FROM CTE  
GROUP BY Range  

Answer 2

Hi @ali mohammed
Try this query:

CREATE TABLE #TEST (Amount INT,[receipt number]VARCHAR(20))  
INSERT INTO #TEST VALUES  
(50, 're01'),  
(200, 're02'),  
(12, 're03'),  
(13, 're04')  
  
SELECT CONCAT((Amount/50)*50, '-' ,(Amount/50)*50 +50) AS Range  
      ,SUM(Amount) AS Amount,COUNT(*) AS [receipt count]  
FROM #TEST  
GROUP BY Amount/50  
ORDER BY Amount/50  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.