How do you create a totals row with percentage in SQL

Question

I have SQL that generates output like that shown in the attached image under "FIGURE A", but need to expand it to look like "FIGURE B".

Can anyone assist with the SQL? Thanks

Answer 1

don't know what Database you are using so try Oracle. First you need to create a new table include the new column PERCENT_COMPLETED

    select STATE,WIDGETS_REQUIRED,WIDGETS_COMPLETED,
           WIDGETS_COMPLETED/WIDGETS_REQUIRED*100 | '%' as PERCENT_COMPLETED
    from table;

then you need to insert a new row into it

   select 
      'Total' as STATE, 
      sum(WIDGETS_REQUIRED) as WIDGETS_REQUIRED, 
      sum(WIDGETS_COMPLETED) as WIDGETS_COMPLETED,
      sum(WIDGETS_COMPLETED)/sum(WIDGETS_REQUIRED)*100 | '%' as PERCENT_COMPLETED 
  into new_table
  from table;

Answer 2

In MYSQL, I tried this solution. First, insert a row with 3 values: STATE, WIDGETS_REQUIRED, WIDGETS_COMPLETED. You can insert these 3 values later, but it is more convenient insert them now.

insert into FIGURE_A values
("TOTAL", 48, 33);

Second, add a column: percent_complete

alter table FIGURE_A
ADD percent_complete decimal(5,2);

Here you should notice that you need to add a constraint. See this question.

So, we need to add the check constraint:

alter table FIGURE_A    
ADD constraint chk_range CHECK (percent_complete>=0 and percent_complete<=100);

Finally, we just add the percentage values. See here. There are many ways to calculate the percentage. And this question is more suitable for your case, because you need to update, not just calculate.

update FIGURE_A
set percent_complete = WIDGETS_COMPLETED*100/WIDGETS_REQUIRED

The final result may look like this.