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

You can use CREATE SEQUENCE t-sql.
For details.
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver16

Answer 3

Hi @ali mohammed
How about like this：

    select [range] ='0-50' ,[count] =COUNT (*),[amount] =SUM(Amount) from #TEST where Amount <=50  
	union all  
	select [range] ='50-100' ,[count] =COUNT (*),[amount] =SUM(Amount) from #TEST where Amount >50 AND Amount <=100  
	union all  
	select [range] ='100-200' ,[count] =COUNT (*),[amount] =SUM(Amount) from #TEST where  Amount >100 AND Amount <=200  

Result:

Best Regards,
Isabella

Answer 4

HI

Check this example

https://www.techonthenet.com/sql_server/sequences.php

Answer 5

First, you have added quite a few tags to your post. In which environment, do you want to run this? You have included by SQL Server and SQLite, but they are two different engines, and what runs on one, may not run on the other.

Next, I am not really able to understand, what you are looking for. Well, I can make a guess, but I cannot map the sample data with the expected output. But it seems that you want to divide you receipts into groups based on the amount divided by 50, and the compute the total amount and the number of receipts in that group.

Here is an untested query to do this in SQL Server (the query may also run on SQLite, but I cannot vouch for that).

   ; WITH ranges AS (  
       SELECT amount, convert(int, amount) / 50 AS rangelow,  
              convert(int, amount) / 50 +  1 AS rangehigh   
       FROM   tbl  
   )  
   SELECT rangelow, rangehigh, SUM(amount) AS total, COUNT(*) AS receipt_count  
   FROM   ranges  
   GROUP  BY rangelow, rangehigh  

Had you supplied CREATE TABLE + INSERT for your table and data, together with the expected result, I would have been able to test the query.