Click here to Skip to main content
15,068,929 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
**Sample data :**
LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS FAMILY CACHE_FAMILY Count  Percentage 

    23053B 00000 00000 00000 S         SUMMIT     WER           43   ??
    23053B 00000 00000 00000 T         SUMMIT     WER           144  ??
    23053B 00000 00000 00684 T         SUMMIT     WER           2    ??
    23053B 00353 00418 00684 T         SUMMIT     WER           1    ??
    23053B 00353 00418 00763 T         SUMMIT     WER           1    ??
    23053B 00353 00418 01512 T         SUMMIT     WER           1    ??
    23053B 00353 00418 06797 T         SUMMIT     WER           1    ??
    23053B 00353 00418 30228 T         SUMMIT     WER           1    ??
    23053B 00353 00418 31935 T         SUMMIT     WER           2    ??
    23053B 05601 01402 00758 T         SUMMIT     WER           1    ??
    23053B 05601 01402 09091 T         SUMMIT     WER           1    ??
    23053B 05601 01402 65053 T         SUMMIT     WER           1    ??
    -------------------------------------------------------------------
    23054B 00000 00000 00000 S        SUMMIT      WER           37   ?? 
    23054B 00000 00000 00000 T        SUMMIT      WER           96   ?? 
    23054B 00353 00418 00758 T        SUMMIT      WER           1    ?? 
    23054B 00354 00414 01095 T         SUMMIT     WER           1    ?? 

This is my query:
SQL
SELECT LINE_NO,
    E_FIELD,
    F_FIELD,
    G_FIELD,
    HSA_STATUS,
    FAMILY,
    CACHE_FAMILY,
    Count = ((SUM(TOTAL)) )  
FROM
    (
      SELECT LINE_NO,
        E_FIELD,
        F_FIELD,G_FIELD,
        HSA_STATUS,
        FAMILY,
        CACHE_FAMILY, 
        Count(LINE_NO) as Total 
      FROM TX_HSA_SUMM 
      WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00') AND ('2013-03-08 10:20:00') 
      GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY
    ) as a
    GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY,total
    ORDER BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY,total

This is my sample data actually. I already make the count. As You can see the column header. My problem is I need to sum the count and the for each row I need to get the percentage. For example the total for the above record is **199** so for the first record count is **43** so the calculation should be **43/199 * 100**. How can I view the percentage? The ?? mean column to get percentage. Please help me I need this urgently.it should be done only for that particular line line after the dash its a new line no so when calculating the percentage it should count only that particular line no count and get the percentage.

Is there any suggestion to show how to get the count first. then from that sum the count and finally. the count should be divided with the sum and * 100 to get percentage.

25-3-2013 changes made for today is:
I manage to get the percentage ready. Is there any idea how to do partition according with the different line number and the count value should be in desc order. Thanks
Posted
Updated 24-Mar-13 21:14pm
v7
Comments
gvprabu 21-Mar-13 5:38am
   
Give some sample Output. Check my Solutions it is OK for U.
Maciej Los 25-Mar-13 3:41am
   
kumar2413, you've got your answer. Please, post updated part as another question.

Hi,

Check the script... U will write direct SELECT Statement with out GROUP BY Clause...

SQL
DECLARE @TX_HSA_SUMM  TABLE(LINE_NO VARCHAR(20), E_FIELD VARCHAR(20), F_FIELD VARCHAR(20), G_FIELD VARCHAR(20), HSA_STATUS CHAR(1), FAMILY VARCHAR(20),
CACHE_FAMILY VARCHAR(20), Count INT)
 
INSERT INTO @TX_HSA_SUMM(LINE_NO, E_FIELD, F_FIELD, G_FIELD, HSA_STATUS, FAMILY, CACHE_FAMILY, Count)
SELECT '23053B','00000','00000','00000','S','SUMMIT','WER',43
UNION ALL
SELECT '23053B','00000','00000','00000','T','SUMMIT','WER',144
UNION ALL
SELECT '23053B','00000','00000','00684','T','SUMMIT','WER',2
UNION ALL
SELECT '23053B','00353','00418','00684','T','SUMMIT','WER',1
UNION ALL
SELECT '23053B','00353','00418','00763','T','SUMMIT','WER',1
UNION ALL
SELECT '23053B','00353','00418','01512','T','SUMMIT','WER',1
UNION ALL
SELECT '23053B','00353','00418','06797','T','SUMMIT','WER',1
UNION ALL
SELECT '23053B','00353','00418','30228','T','SUMMIT','WER',1
UNION ALL
SELECT '23053B','00353','00418','31935','T','SUMMIT','WER',2
UNION ALL
SELECT '23053B','05601','01402','00758','T','SUMMIT','WER',1
UNION ALL
SELECT '23053B','05601','01402','09091','T','SUMMIT','WER',1
UNION ALL
SELECT '23053B','05601','01402','65053','T','SUMMIT','WER',1

SELECT LINE_NO, E_FIELD, F_FIELD, G_FIELD, HSA_STATUS, FAMILY, CACHE_FAMILY, Count FROM @TX_HSA_SUMM

SELECT LINE_NO,
        E_FIELD,
        F_FIELD,G_FIELD,
        HSA_STATUS,
        FAMILY,
        CACHE_FAMILY, 
        CAST(ROUND(((Count/T.SumofCount) *100.00),2) AS NUMERIC(10,2)) 'Percentage'
FROM @TX_HSA_SUMM 
INNER JOIN (SELECT SUM(Count)* 1.0 'SumofCount' 
            FROM @TX_HSA_SUMM
            WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00') AND ('2013-03-08 10:20:00')
) T ON 1=1
WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00') AND ('2013-03-08 10:20:00') 

Regards,
GVPrabu
   
v3
You need something like this:
SQL
SELECT t1.LINE_NO, t1.E_FIELD, t1.F_FIELD, t1.G_FIELD, t1.HSA_STATUS, t1.FAMILY, t1.CACHE_FAMILY, SUM(t1.TOTAL) AS CountOfLineNo, t2.TotalOfLineNo, (SUM(t1.TOTAL) / t2.TotalOfLineNo * 100) AS Percentage
FROM (
      SELECT LINE_NO, E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY, Count(LINE_NO) as Total
      FROM TX_HSA_SUMM
      WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00' AND '2013-03-08 10:20:00')
      GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY
    ) AS t1 LEFT JOIN (
                      SELECT LINE_NO, COUNT(LINE_NO) AS TotalOfLineNo
                      FROM TX_HSA_SUMM
                      WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00' AND '2013-03-08 10:20:00')
                      ) AS t2 ON t1.LINE_NO = t2.LINE_NO
    GROUP BY t1.LINE_NO, t1.E_FIELD, t1.F_FIELD, t1.G_FIELD, t1.HSA_STATUS, t1.FAMILY, t1.CACHE_FAMILY, t1.Total
    ORDER BY t1.LINE_NO, t1.E_FIELD, t1.F_FIELD, t1.G_FIELD, t1.HSA_STATUS, t1.FAMILY, t1.CACHE_FAMILY, t1.Total

Probably, you'll need to CONVERT/CAST[^] Percentage column to expected format.
   
Comments
gvprabu 21-Mar-13 5:35am
   
Hi Maciej,
He Tried some complex way for Finding Total Count... check my solution... :-)
kumar2413 21-Mar-13 21:12pm
   
Your query works fine man. Thanks alot after some modification but there is still some problem. The percentage value showing all 0 only.so i try to work out with that. Thanks anyway to spend your time to help me.
Maciej Los 22-Mar-13 2:34am
   
You're welcome ;)

Try this: CONVERT(DECIMAL(10,2),(SUM(t1.TOTAL) / t2.TotalOfLineNo *100)) AS Percentage or CAST((SUM(t1.TOTAL) / t2.TotalOfLineNo *100) AS NUMERIC(10,2)) AS Percentage
Try this:
SQL
SELECT 
  B.*
, Percentage= Count *100.0 /SUM(Count) OVER (PARTITION BY 1)
FROM
(
SELECT LINE_NO,
    E_FIELD,
    F_FIELD,
    G_FIELD,
    HSA_STATUS,
    FAMILY,
    CACHE_FAMILY,
    Count = ((SUM(TOTAL)) )
FROM
    (
      SELECT LINE_NO,
        E_FIELD,
        F_FIELD,G_FIELD,
        HSA_STATUS,
        FAMILY,
        CACHE_FAMILY,
        Count(LINE_NO) as Total
      FROM TX_HSA_SUMM
      WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00') AND ('2013-03-08 10:20:00')
      GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY
    ) as a
    GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY,total
)as B
 ORDER BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY,Count
   
v2
Comments
kumar2413 21-Mar-13 21:11pm
   
I dont know why the over() is giving me error during the execution.
SQL
SELECT t1.LINE_NO, t1.E_FIELD, t1.F_FIELD, t1.G_FIELD, t1.HSA_STATUS, t1.FAMILY, t1.CACHE_FAMILY, 
SUM(t1.TOTAL) AS CountOfLineNo, t2.TotalOfLineNo, (convert(decimal(10,2),SUM(t1.TOTAL)) * 100 / t2.TotalOfLineNo ) AS Percentage
FROM (
      SELECT LINE_NO, E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY, Count(LINE_NO) as Total
      FROM TX_HSA_SUMM
      WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00') AND ('2013-03-08 10:20:00')
      GROUP BY LINE_NO,E_FIELD,F_FIELD,G_FIELD,HSA_STATUS,FAMILY,CACHE_FAMILY
    ) AS t1 LEFT JOIN (
                      SELECT LINE_NO, COUNT(LINE_NO) AS TotalOfLineNo
                      FROM TX_HSA_SUMM
                      WHERE MT_TIMESTAMP2 BETWEEN ('2013-03-07 10:10:00') AND ('2013-03-08 10:20:00')
                      GROUP BY LINE_NO
                      ) AS t2 ON t1.LINE_NO = t2.LINE_NO
    GROUP BY t1.LINE_NO, t1.E_FIELD, t1.F_FIELD, t1.G_FIELD, t1.HSA_STATUS, t1.FAMILY, t1.CACHE_FAMILY, t1.Total,t2.TotalOfLineNo
    ORDER BY t1.LINE_NO, t1.E_FIELD, t1.F_FIELD, t1.G_FIELD, t1.HSA_STATUS, t1.FAMILY, t1.CACHE_FAMILY, t1.Total,t2.TotalOfLineNo
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900