SQL Server How do I show my data as in interval form in SQL?

2ledvvac  于 2023-05-28  发布在  其他
关注(0)|答案(3)|浏览(172)

I have a table like this:
| ID | Class | Marks |
| ------------ | ------------ | ------------ |
| 1 | 12th | 0 |
| 2 | 10th | 25 |
| 3 | 09th | 24 |
| 4 | 12th | 50 |
| 5 | 10th | 60 |
| 6 | 09th | 70 |

The desired output is like this:

MarksCLass12thclass9thclass10th
0-25111
25-50100
50-60101
60-70011
Total323

How can I do the same with SQL?

pjngdqdw

pjngdqdw1#

CREATE TABLE marks 
  ( 
     id    INT, 
     class VARCHAR(200), 
     marks INT 
  ); 

INSERT INTO marks 
VALUES     (1, 
            '12th', 
            0); 

INSERT INTO marks 
VALUES     (1, 
            '10th', 
            25); 

INSERT INTO marks 
VALUES     (1, 
            '9th', 
            24); 

INSERT INTO marks 
VALUES     (1, 
            '12th', 
            50); 

INSERT INTO marks 
VALUES     (1, 
            '10th', 
            60); 

INSERT INTO marks 
VALUES     (1, 
            '9th', 
            70);

-----not able to put (0-24) condition in marks as it is actually minus the value as -24 -----

SELECT CASE 
         WHEN marks >= 0 
              AND marks < 25 THEN ( 024 ) 
         WHEN marks >= 25 
              AND marks <= 50 THEN ( 2550 ) 
         WHEN marks >= 51 
              AND marks < 60 THEN ( 5160 ) 
         WHEN marks >= 60 
              AND marks < 71 THEN ( 6070 ) 
         ELSE NULL 
       END            AS marks, 
       Sum(class12th) AS CLass12th, 
       Sum(class10th) AS CLass9th, 
       Sum(class9th)  AS CLass9th 
FROM   (SELECT id, 
               marks, 
               [12th] AS CLass12th, 
               [10th] AS CLass10th, 
               [9th]  AS CLass9th 
        FROM   (SELECT id, 
                       class, 
                       marks 
                FROM   marks) AS SourceTable 
               PIVOT ( Count(class) 
                     FOR class IN ([12th], 
                                   [10th], 
                                   [9th]) ) AS pivottable)a 
GROUP  BY marks
ny6fqffe

ny6fqffe2#

Try this :

select '0-25', sum(case when class = '12th' then 1 else 0 end) '12th',sum(case when class = '10th' then 1 else 0 end) '10th',sum(case when class = '9th' then 1 else 0 end) '9th'
from Yourtable where marks >= 0 and marks < = 25
union
select '25-50', sum(case when class = '12th' then 1 else 0 end),sum(case when class = '10th' then 1 else 0 end),sum(case when class = '9th' then 1 else 0 end)
from Yourtable where marks >= 25 and marks < = 50
union
select '50-60', sum(case when class = '12th' then 1 else 0 end),sum(case when class = '10th' then 1 else 0 end),sum(case when class = '9th' then 1 else 0 end)
from Yourtable where marks >= 50 and marks < = 60
union
select '60-70', sum(case when class = '12th' then 1 else 0 end),sum(case when class = '10th' then 1 else 0 end),sum(case when class = '9th' then 1 else 0 end)
from Yourtable where marks >= 60 and marks < = 70
union
select 'total', sum(case when class = '12th' then 1 else 0 end),sum(case when class = '10th' then 1 else 0 end),sum(case when class = '9th' then 1 else 0 end)
from Yourtable
daolsyd0

daolsyd03#

You need to store your ranges our use a CTE and use it in a query like this:

;with ranges as (
    select 0 fromMark, 25 toMark, '0-25' title
    union all select 25,50, '25-50'
    union all select 50,60, '50-60'
    union all select 60,70, '60-70'
    union all select 0,100, 'Total'
)
select 
    r.title,
    count(case when t.Class = '12th' then 1 end) Class12th,
    count(case when t.Class = '09th' then 1 end) Class9th,
    count(case when t.Class = '10th' then 1 end) Class10th
from yourTable t
left join ranges r
  on t.Marks >= r.fromMark and t.Marks < r.toMark
group by 
    r.title;

相关问题