Add row at the end that adds totals of columns in POSTGRESQL

Navigation
0

I have the following query, in postgresql and psycopg2: 

cur.execute("""select admin_eps.nombre_eps, admin_eps.nit_eps,   

                   SUM(COALESCE((sbmEmp/30*diastmes + sbmEmp/240*1.25*hon + sbmEmp/240*1.25*hed 
                   + sbmEmp/240*1.75*hen + sbmEmp/240*2*hofd + sbmEmp/240*2.25*hofn 
                   + sbmEmp/240*2.50*hefd + sbmEmp/240*2.75*hefn)*0.040)) AS DEDU_EPS,

                   SUM(COALESCE((sbmEmp/30*diastmes + sbmEmp/240*1.25*hon + sbmEmp/240*1.25*hed 
                   + sbmEmp/240*1.75*hen + sbmEmp/240*2*hofd + sbmEmp/240*2.25*hofn 
                   + sbmEmp/240*2.50*hefd + sbmEmp/240*2.75*hefn)*0.085)) AS GASTO_EPS

                   from empleado

                   INNER JOIN novedad_nomina ON novedad_nomina.numdocempnov = empleado.numdocemp
                   INNER JOIN admin_eps ON admin_eps.nit_eps = empleado.nit_eps                

                   GROUP BY admin_eps.nit_eps,
                          admin_eps.nombre_eps                                   

                           """)

    conn.commit()
  

What generates the following query:

> 

╒══════════════╤═══════════╤═════════════╤═════════════╕
│ nombre_eps   │   nit_eps │ dedu_eps    │ gasto_eps   │
╞══════════════╪═══════════╪═════════════╪═════════════╡
│ EPS SURA     │ 800088702 │ $48,218.10  │ $102,463.46 │
├──────────────┼───────────┼─────────────┼─────────────┤
│ MEDIMAS EPS  │ 901097473 │ $78,082.88  │ $165,926.12 │
├──────────────┼───────────┼─────────────┼─────────────┤
│ NUEVA EPS    │ 900156264 │ $169,648.51 │ $360,503.09 │
├──────────────┼───────────┼─────────────┼─────────────┤
│ COOMEVA EPS  │ 805000427 │ $146,797.86 │ $311,945.45 │
╘══════════════╧═══════════╧═════════════╧═════════════╛

As I can add to the end of the query, add me or total the columns dedu_eps and gasto_eps, I appreciate what you can guide, this would be the result I'm looking for, thanks: 

╒══════════════╤═══════════╤═════════════╤═════════════╕
│ nombre_eps   │   nit_eps │ dedu_eps    │ gasto_eps   │
╞══════════════╪═══════════╪═════════════╪═════════════╡
│ EPS SURA     │ 800088702 │ $48,218.10  │ $102,463.46 │
├──────────────┼───────────┼─────────────┼─────────────┤
│ MEDIMAS EPS  │ 901097473 │ $78,082.88  │ $165,926.12 │
├──────────────┼───────────┼─────────────┼─────────────┤
│ NUEVA EPS    │ 900156264 │ $169,648.51 │ $360,503.09 │
├──────────────┼───────────┼─────────────┼─────────────┤
│ COOMEVA EPS  │ 805000427 │ $146,797.86 │ $311,945.45 │
╘══════════════╧═══════════╧═════════════╧═════════════╛
**Totales                   $295,949.49   $940,838.12**
    
asked by Jsierra2017 29.03.2018 в 23:10
source

1 answer

0

Try at the end of your query to use the options of CUBE and ROLLUP, I think they would serve you to execute what you need. link

    
answered by 30.03.2018 в 00:13
Why is my report viewer "loading"? Why do not the Fontastic icons appear?