Sample data :
LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS FAMILY CACHE_FAMILY Count
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
This is my query:
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? Please help me I need this urgently.
Best Answer
This is a great example of how the Window Functions can be used. Based on your data, I'm assuming you want to do this for every unique LINE_NO. I'll leave it to you to format the percentage column.