Agregate multiple columns in postgres

Question

I have a trouble with values to agregate. My Table looks like this :

CREATE TABLE test2 (a_date date, a_actif integer[], a_registred   integer[], a_filter integer, sum_actions integer );
INSERT INTO test2 VALUES
('2016-12-15', array[1,2,3], array[1,4], 5, 2),
('2016-12-15', array[5], array[1,4], 2, 20),
('2016-12-15', array[6,2,3], array[2,3], 3, 10),
('2016-12-15', array[8,2,3], array[4,1], 1, 4);

The goal is to count the distinct a_actif and a_registred, and get the sum, per date, of the number of actions. This should looks like this :

-----------------------------------------------------------
|  Date    |  Active_count | Register_count | sum_actions |
-----------------------------------------------------------
|2016-12-15|    6          |      4         |  36         |
-----------------------------------------------------------

Active_count : DISTINCT id of a_actif

Register_count : DISTINCT id of a_registred

So I did something like this, but the sum of actions is wrong

SELECT f.date, COUNT(DISTINCT f.actifs), COUNT(DISTINCT f.registers), SUM(sum_actions)
FROM
(   
 SELECT unnest(a_actif) as actifs, a_date as date, unnest(a_registred) as registers, sum_actions
FROM test2
WHERE a_filter IN ('1','2','3','5')
) f
--WHERE date BETWEEN XX and YY
GROUP BY f.date;

Any ideas ?

http://rextester.com/CXWG2246

check your unnestwithout group by rextester.com/OTMU9161 is obvious why you get wrong sum. Just calculate the sum in a separated subquery. let me know if need more guidelines — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 15, 2016 at 17:24

Juan Carlos Oropeza · Accepted Answer · 2016-12-15 17:31:14Z

1

SQL DEMO

WITH t_count as (
    SELECT f.date, COUNT(DISTINCT f.actifs), COUNT(DISTINCT f.registers)
    FROM
    (   
        SELECT  unnest(a_actif) as actifs, 
                a_date as date, 
                unnest(a_registred) as registers
        FROM test2
        WHERE a_filter IN ('1','2','3','5')
    ) f
    --WHERE date BETWEEN '2016-09-01' AND '2016-09-01'
    GROUP BY f.date
), t_sum as (
    SELECT a_date as date, SUM(sum_actions) total
    FROM test2
    WHERE a_filter IN ('1','2','3','5')    
    --WHERE date BETWEEN '2016-09-01' AND '2016-09-01'
    GROUP BY date
)        
SELECT  t1.*, t2.total
FROM t_count t1
JOIN t_sum   t2
  ON t1.date = t2.date

OUTPUT

answered Dec 15, 2016 at 17:31

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Agregate multiple columns in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related