Problem with consultation in postgresql crosstab

Question

Problem with consultation in postgresql crosstab

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Good day, I have a postgresql query but I need to modify its structure from columns to rows:

select anno, SUM(total_sales) as sumas, sexo_
   from sivigila_a 
   where cod_event = 110 
   GROUP BY anno, sexo_
    ORDER  BY 1

the result is the following:

and I need to convert it to this but with the totals of the previous sum

The problem is that the information does not appear and I really do not understand how to perform the corosstab

SELECT * FROM   crosstab(
   'select anno, SUM(total_sales) as sumas, sexo_
   from sivigila_a 
   where cod_event = 110 
   GROUP BY anno, sexo_
    ORDER  BY 1',$$VALUES ('F'::text), ('M'::text)$$
   )AS x (anno text, "F" text, "M" text);

I hope it can be solved. Thanks

postgresql

asked by Sebastian 54 05.05.2017 в 18:46

source

2 answers

How to know which object is the one on the list [closed] How to synchronize C threads with OpenMP

score 1 · Answer 1

The problem with the crosstab is that it is very picky about the position of the columns in the query and the types of data.

I see two problems in your attempt.

The column of the values to pivot must go last in the list therefore SUM(total_sales) must be last in the list of SELECT

The type of sexo_ , character (2) according to your example, must match the query of categories and the definition of the output columns, both are text .

The final query should be

SELECT * FROM   crosstab(   
  $$
  select
    anno, 
    sexo_::text,
    SUM(total_sales) as sumas
  from sivigila_a 
  where cod_event = 110 
  GROUP BY anno, sexo_
  ORDER  BY 1
  $$,
  $$
  VALUES ('F'::text), ('M'::text)
  $$
)AS x (anno text, "F" text, "M" text);

Which results

 anno |  F  | M 
------+-----+---
 2112 | 216 | 
 2113 | 259 | 
 2114 | 192 | 1
 2115 | 219 | 1
 2116 | 266 | 
 2117 | 79  | 
(6 rows)

score 0 · Answer 2

Well, consulting I was able to make a query but not with crosstab ().

SELECT anno, 
SUM(CASE sexo_ WHEN 'F' THEN total_sales ELSE 0 END) AS fem, 
SUM(CASE sexo_ WHEN 'M' THEN total_sales ELSE 0 END) AS masc 
FROM sivigila_a 
where cod_event = 110 
GROUP BY anno 
ORDER BY anno

Resulting in the expected

Although if someone knows how it could be done with cosstab () it would be very helpful for later exercises. Thanks