Home > Server > PostgreSQL Total, Subtotal an alternative

PostgreSQL Total, Subtotal an alternative

Using multiple select and TEMPORARY TABLE to implement Total and Subtotal
in PostgreSQL SQL

  DROP TABLE IF EXIST tmp_aqy ;
  CREATE TEMPORARY TABLE tmp_aqy
   (
      description  character varying, 
      _type int , 
      _year bigint,
      _month bigint, 
      _count bigint, 
      _avg bigint        
   ) 
   -- ON COMMIT DELETE ROWS;
   ON COMMIT PRESERVE ROWS;  -- rows delete at end sections 

   BEGIN TRANSACTION;

   INSERT INTO tmp_aqy    
	SELECT 'month Enquiry.' , 6 , date_part('year',createddate) As year , 
               date_part('month',createddate) as month, 
               count(Cs.itemid) AS conta , null 

	FROM contacts_enquiry  Cs
	GROUP BY date_part('year', createddate), date_part('month', createddate)
	ORDER BY date_part('year', createddate) DESC ,  
        date_part('month', createddate) DESC,  conta DESC
	-- LIMIT 60 
	;

   -- SELECT COUNT(*) FROM tmp_aqy;
   INSERT INTO tmp_aqy
    ( SELECT 'avg(month) reg.', 10, _year, null, null, avg(_count) FROM tmp_aqy
    GROUP BY  _year 
    ORDER BY  _year DESC) ;    

   INSERT INTO tmp_aqy
    ( SELECT 'year reg.', 15, _year, null, sum(_count), null FROM tmp_aqy
    GROUP BY  _year 
    ORDER BY  _year DESC) ;

    INSERT INTO tmp_aqy
    ( SELECT 'Total Enquiries ', 32, null, null, sum(_count), null FROM tmp_aqy         
      WHERE _type = 15 
      GROUP BY  _type   
    ) ;   

    INSERT INTO tmp_aqy
    ( SELECT 'Tot. parz.', 31, null, null, sum (_count), null FROM tmp_aqy         
      WHERE _type = 15   and _year > 2009  
      GROUP BY  _type   
    ) ;   

   COMMIT;

   SELECT description, _type, _year, _month, _count, _avg  FROM tmp_aqy
   WHERE _year > 2009 OR _type  > 30 
    GROUP BY description , _type ,  _year , _month, _count, _avg
    ORDER BY  _type DESC,  _year DESC, _month DESC, _count DESC ;

Risorse:

SQL  – PostgreSQL

Postgresql 

Plpgsql 

DB

Creare eliminare database e tabelle

Annunci
Categorie:Server Tag:, ,
  1. Non c'è ancora nessun commento.
  1. No trackbacks yet.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: