Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

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.

SELECT      LINE_NO,
            E_FIELD,
            F_FIELD,
            G_FIELD,
            HSA_STATUS,
            FAMILY,
            CACHE_FAMILY,
            COUNT(*) AS CT,
            COUNT(*) / SUM(COUNT(*) * 1.0) OVER (PARTITION BY LINE_NO)

FROM        TX_HSA_SUMM

GROUP BY    LINE_NO,
            E_FIELD,
            F_FIELD,
            G_FIELD,
            HSA_STATUS,
            FAMILY,
            CACHE_FAMILY
share|improve this answer
    
The Count column is not part of the sample data, even though the OP included it in that section. –  Jon Seigel Jul 27 '13 at 22:10
    
Jon - ah, you are correct, I missed that at first. I tweaked the query, I believe that should get the OP what he's looking for. –  Henry Lee Jul 27 '13 at 23:36
add comment

Simplest way I believe is you can wrap around your statement with another SELECT statement and do Count / SUM(Count) * 100 to get your percentage column. Hope that makes sense.

UPDATE:

Try this:

create table #results (LINE_NO VARCHAR(50), E_FIELD VARCHAR(50), F_FIELD VARCHAR(50), G_FIELD VARCHAR(50), HSA_STATUS VARCHAR(50), FAMILY VARCHAR(50), CACHE_FAMILY VARCHAR(50), Count INT)

insert into #results
    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

declare @totalcnt int
select @totalcnt = sum(count) from #results

select *, (count / cast(@totalcnt as float)) * 100 as CountPercent from #results

That should give you the additional column with percentage values per row.

share|improve this answer
    
i have do that also but when i reduce the number of columns im getting only the total count that is the problem. –  sentil kumar Mar 21 '13 at 1:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.