Clever Geek Handbook

Percentage of labels in different character ranges

I have a table that looks like this:

enter image description here

For each grading code, I would like to create a query that returns the percentage of tags in specific grade ranges ... For example

 FileYear   AssessmentCode   MarkRange   MarkPercentage

   2014        11cpssdd        0-10           5.88
   2014        11cpssdd        11-20          0
   2014        11cpssdd        21-30          0
   2014        11cpssdd        31-40          0
   2014        11cpssdd        41-50          0
   2014        11cpssdd        51-60          0
   2014        11cpssdd        61-70          0
   2014        11cpssdd        71-80          29.4
   2014        11cpssdd        81-90          52.9
   2014        11cpssdd        91-100         11.7

Not sure how to do this at all and was wondering if anyone could help? Thanks in advance for any help.

+3


source to share


5 answers


You can do this by calculating SUM

each range and then the percentage of that SUM

total SUM

. This is a complete working example:

SET NOCOUNT ON
GO

    DECLARE @DataSource TABLE
    (
         [FileYear] CHAR(4)
        ,[AssessmentCode] CHAR(8)
        ,[StudentID] INT
        ,[Mark] TINYINT
    )   

    INSERT INTO @DataSource ([FileYear], [AssessmentCode], [StudentID], [Mark])
    VALUES ('2014', '11cpssdd', '34323', '75')
          ,('2014', '11cpssdd', '74666', '38')
          ,('2014', '11cpssdd', '87664', '34')
          ,('2014', '11cpssdd', '87576', '66')
          ,('2014', '11cpssdd', '23455', '87')
          ,('2014', '11cpssdd', '87654', '75')
          ,('2014', '11cpssdd', '98776', '75')
          ,('2014', '11cpssdd', '34543', '55')
          ,('2014', '11ecoeco', '87687', '89')
          ,('2014', '11ecoeco', '56466', '77')
          ,('2014', '11ecoeco', '34544', '45')
          ,('2014', '11ecoeco', '95554', '23')
          ,('2014', '11ecoeco', '22322', '56')
          ,('2014', '11ecoeco', '76557', '66')

    SELECT [FileYear]
          ,[AssessmentCode]
          ,[MarkPercentage]
          ,[MarkRange] * 100 /[ALL] AS [MarkRange]
    FROM
    (
        SELECT [FileYear]
              ,[AssessmentCode]
              ,SUM(IIF([Mark] BETWEEN 0 AND 10, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 11 AND 20, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 21 AND 30, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 31 AND 40, [Mark], 0))
              ,SUM(IIF([Mark] BETWEEN 41 AND 50, [Mark], 0))
              ,SUM(IIF([Mark] BETWEEN 51 AND 60, [Mark], 0))
              ,SUM(IIF([Mark] BETWEEN 61 AND 70, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 71 AND 80, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 81 AND 90, [Mark], 0))
              ,SUM(IIF([Mark] BETWEEN 91 AND 100, [Mark], 0)) 
              ,SUM([Mark])
        FROM @DataSource
        GROUP BY [FileYear]
                ,[AssessmentCode]
    ) DataSource ([FileYear], [AssessmentCode], [0-10], [11-20], [21-30], [31-40], [41-50], [51-60], [61-70], [71-80], [81-90], [91-100], [ALL])
    UNPIVOT
    (
        [MarkRange] FOR [MarkPercentage] IN ([0-10], [11-20], [21-30], [31-40], [41-50], [51-60], [61-70], [71-80], [81-90], [91-100])
    )PVT
    ORDER BY [FileYear]
            ,[AssessmentCode]
            ,[MarkPercentage]

SET NOCOUNT OFF
GO

enter image description here



Check out this solution. Please note that we are filtering for specific AssesmentCode

. Also, if you want a formatted result decimal

, just type [MarkRange]

before dividing.

SET NOCOUNT ON
GO

    DECLARE @DataSource TABLE
    (
         [FileYear] CHAR(4)
        ,[AssessmentCode] CHAR(8)
        ,[StudentID] INT
        ,[Mark] TINYINT
    )   

    INSERT INTO @DataSource ([FileYear], [AssessmentCode], [StudentID], [Mark])
    VALUES ('2014', '11CPSSDD', '34323', '93')
          ,('2014', '11CPSSDD', '74666', '93')
          ,('2014', '11CPSSDD', '87664', '90')
          ,('2014', '11CPSSDD', '87576', '90')
          ,('2014', '11CPSSDD', '23455', '89')
          ,('2014', '11CPSSDD', '87654', '86')
          ,('2014', '11CPSSDD', '98776', '84')
          ,('2014', '11CPSSDD', '34543', '84')
          ,('2014', '11CPSSDD', '87687', '84')
          ,('2014', '11CPSSDD', '56466', '83')
          ,('2014', '11CPSSDD', '34544', '82')
          ,('2014', '11CPSSDD', '95554', '80')
          ,('2014', '11CPSSDD', '22322', '79')
          ,('2014', '11CPSSDD', '76557', '78')
          ,('2014', '11CPSSDD', '76557', '77')
          ,('2014', '11CPSSDD', '76557', '76')
          ,('2014', '11CPSSDD', '76557', '70')

    SELECT [FileYear]
          ,[AssessmentCode]
          ,[MarkPercentage]
          ,[MarkRange] * 100 /[ALL] AS [MarkRange]
    FROM
    (
        SELECT [FileYear]
              ,[AssessmentCode]
              ,SUM(IIF([Mark] BETWEEN 0 AND 10, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 11 AND 20, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 21 AND 30, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 31 AND 40, 1, 0))
              ,SUM(IIF([Mark] BETWEEN 41 AND 50, 1, 0))
              ,SUM(IIF([Mark] BETWEEN 51 AND 60, 1, 0))
              ,SUM(IIF([Mark] BETWEEN 61 AND 70, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 71 AND 80, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 81 AND 90, 1, 0))
              ,SUM(IIF([Mark] BETWEEN 91 AND 100, 1, 0)) 
              ,COUNT([Mark])
        FROM @DataSource
        WHERE [AssessmentCode] = '11CPSSDD'
        GROUP BY [FileYear]
                ,[AssessmentCode]
    ) DataSource ([FileYear], [AssessmentCode], [0-10], [11-20], [21-30], [31-40], [41-50], [51-60], [61-70], [71-80], [81-90], [91-100], [ALL])
    UNPIVOT
    (
        [MarkRange] FOR [MarkPercentage] IN ([0-10], [11-20], [21-30], [31-40], [41-50], [51-60], [61-70], [71-80], [81-90], [91-100])
    )PVT
    ORDER BY [FileYear]
            ,[AssessmentCode]
            ,[MarkPercentage]

SET NOCOUNT OFF
GO
+1


source


You can use the CASE statement to handle the conditional selection you want. You will need one for the MarkRange column and one for the MarkPercentage return.



See http://msdn.microsoft.com/en-us/library/ms181765.aspx

0


source


This is not an exact answer, but it should help you, I am not sure how you have this percentage. so I commented that the u part can tweak the bit bit, get the percentage.

CREATE TABLE #test
  (
     FileYear       INT,
     AssessmentCode VARCHAR(50),
     StudentID      INT,
     Mark           INT
  )

INSERT INTO #test
VALUES      ( 2014,'11cpssdd',34323,75),( 2014,'11cpssdd',74666,38 ),
            ( 2014,'11cpssdd',87664,34),( 2014,'11cpssdd',87576,66),
            ( 2014,'11cpssdd',23455,87),( 2014,'11cpssdd',87654,75),
            ( 2014,'11cpssdd',98776,75),( 2014,'11cpssdd',34543,55),
            ( 2014,'11ecoeco',87687,89),( 2014,'11ecoeco',56466,77),
            ( 2014,'11ecoeco',34544,45),( 2014,'11ecoeco',95554,23),
            ( 2014,'11ecoeco',22322,56),( 2014,'11ecoeco',76557,66) 

;WITH cte
     AS (SELECT DISTINCT 0  AS fst,
                         10 AS scd,
                         fileyear,
                         AssessmentCode
         FROM   #test
         WHERE  AssessmentCode = '11cpssdd'
         UNION ALL
         SELECT scd + 1 fst,
                scd + 10,
                fileyear,
                AssessmentCode
         FROM   cte
         WHERE  scd < 100)
SELECT b.FileYear,
       b.AssessmentCode,
       CONVERT(VARCHAR(10), fst) + ' - '
       + CONVERT(VARCHAR(10), scd) MarkRange,
       count(case when mark IS not null then 1 end) Range_Count
       --(count(case when mark IS not null then 1 end)/10.0)*100 percentage
FROM   cte b
       LEFT JOIN(SELECT CASE
                          WHEN mark BETWEEN 0 AND 10 THEN '0 - 10'
                          WHEN mark BETWEEN 11 AND 20 THEN '11 - 20'
                          WHEN mark BETWEEN 21 AND 30 THEN '21 - 30'
                          WHEN mark BETWEEN 31 AND 40 THEN '31 - 40'
                          WHEN mark BETWEEN 41 AND 50 THEN '41 - 50'
                          WHEN mark BETWEEN 51 AND 60 THEN '51 - 60'
                          WHEN mark BETWEEN 61 AND 70 THEN '61 - 70'
                          WHEN mark BETWEEN 71 AND 80 THEN '71 - 80'
                          WHEN mark BETWEEN 81 AND 90 THEN '81 - 90'
                          WHEN mark BETWEEN 90 AND 100 THEN '90 - 100'
                        END [range],
                        FileYear,
                        mark,
                        AssessmentCode
                 FROM   #test
                 WHERE  AssessmentCode = '11cpssdd') a
              ON CONVERT(VARCHAR(10), fst) + ' - '
                 + CONVERT(VARCHAR(10), scd) = [range]
                 AND a.AssessmentCode = b.AssessmentCode
GROUP  BY b.FileYear,
          b.AssessmentCode,
          CONVERT(VARCHAR(10), b.fst) + ' - '
          + CONVERT(VARCHAR(10), b.scd)
0


source


  • Declare 10 variables for each range.
  • Use the cursor on "MARK" and increase the relative variable by 1.
  • Do the math. variable = variable / 17.
  • Update the variables accordingly in the new "MARK RANGE" column.
  • Good luck !!
0


source


Here's another way to get your result.

;WITH a AS
(
    SELECT FileYear, AssessmentCode, MarkCount = COUNT(*)
    FROM #1
    GROUP BY FileYear, AssessmentCode
)
, b AS
(
    SELECT FileYear, AssessmentCode, Tenth = (Mark - 1)/10 + 1, MarkCount = COUNT(*)
    FROM #1
    GROUP BY FileYear, AssessmentCode, (Mark - 1)/10 + 1
)
SELECT a.FileYear, a.AssessmentCode
, MarkPercentage = CONVERT(varchar(3), Tenth * 10 - IIF(Tenth=1,10,9)) + '-'
                   + CONVERT(varchar(3), Tenth * 10)
, MarkRange = CONVERT(float, LEFT(100.0 * b.MarkCount / a.MarkCount, 4))
FROM a
JOIN b ON a.FileYear = b.FileYear AND a.AssessmentCode = b.AssessmentCode
ORDER BY a.FileYear, a.AssessmentCode
0


source






More articles:


All Articles