Need to convert MySQL query to PostgreSQL

Question

I have a table of companies that have events. I need to create a summary table that shows the active companies by month, meaning how many companies have at least one or more events in every month. Any company that has an event in a month is considered active.

I was able to do this detailed count table with a summary of active companies as a total at the end, but I can't recreate this in PostgreSQL.

Also this report has all the companies listed that have events, I would also like a report where ONLY the the total is presented.

CREATE TABLE mysql_test ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
company VARCHAR(30) NOT NULL, 
my_date DATETIME NOT NULL,  
status VARCHAR(50), 
reg_date TIMESTAMP 
); 


INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('1', 'ABC', '2019/1/1', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('2', 'ABC', '2019/1/1', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('3', 'ABC', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('4', 'ABC', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('5', 'DEF', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('6', 'DEF', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('7', 'DEF', '2019/3/3', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('8', 'DEF', '2019/3/3', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('9', 'GHI', '2019/3/3', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('10', 'GHI', '2019/3/3', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('11', 'JKL', '2019/1/1', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('12', 'JKL', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('13', 'JKL', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('14', 'JKL', '2019/1/1', 'event', CURRENT_TIMESTAMP); 

(SELECT company,
        SUM(MONTH(my_date) = 1) as Jan,
        SUM(MONTH(my_date) = 2) as Feb,
        SUM(MONTH(my_date) = 3) as Mar
 FROM mysql_test
 WHERE status = 'Event' AND my_date >= '2019-01-01' AND
       my_date < '2020-10-01'
 GROUP BY company
) UNION ALL
(SELECT 'Total',
        COUNT(DISTINCT CASE WHEN MONTH(my_date) = 1 THEN company END) as Jan,
        COUNT(DISTINCT CASE WHEN MONTH(my_date) = 2 THEN company END) as Feb,
        COUNT(DISTINCT CASE WHEN MONTH(my_date) = 3 THEN company END) as Mar
FROM mysql_test
WHERE status = 'Event' AND my_date >= '2019-01-01' AND
      my_date < '2020-10-01'
);

Try pg_chameleon? (pgchameleon.org)

richyen
– richyen

2019-10-27 05:09:56 +00:00
Commented Oct 27, 2019 at 5:09 — richyen
– richyen, Commented Oct 27, 2019 at 5:09

GMB · Accepted Answer · 2019-10-26 16:12:33Z

This syntax that is not supported in Postgres:

SUM(MONTH(my_date) = 1)

In MySQL, conditions return 0 or 1 when evaluated in numeric context, but not in Postgres. Explicit casting from boolean to integer is needed. Also, function MONTH() does not exists, you can use the more standard EXTRACT(), like so:

SUM( (EXTRACT(month from my_date) = 1)::int )

Also, it would be better to cast dates for comparison (even if I Postgres would probably do it for you if you don't), so this:

my_date >= '2019-01-01'

Could be rewritten:

my_date >= '2019-01-01'::date

Here is a new version for your query:

(
    SELECT company,
        SUM( (EXTRACT(month from my_date) = 1)::int ) as Jan,
        SUM( (EXTRACT(month from my_date) = 2)::int ) as Feb,
        SUM( (EXTRACT(month from my_date) = 3)::int ) as Mar
    FROM mysql_test
    WHERE 
        status = 'event' 
        AND my_date >= '2019-01-01'::date 
        AND my_date <  '2020-10-01'::date
 GROUP BY company
) UNION ALL (
    SELECT 
        'Total',
        COUNT(DISTINCT CASE WHEN EXTRACT(month from my_date) = 1 THEN company END) as Jan,
        COUNT(DISTINCT CASE WHEN EXTRACT(month from my_date) = 2 THEN company END) as Feb,
        COUNT(DISTINCT CASE WHEN EXTRACT(month from my_date) = 3 THEN company END) as Mar
    FROM mysql_test
    WHERE 
        status = 'event' 
        AND my_date >= '2019-01-01'::date 
        AND my_date < '2020-10-01'::date
)

Demo on DB Fiddle:

company | jan | feb | mar
:------ | --: | --: | --:
ABC     |   2 |   2 |   0
DEF     |   0 |   2 |   2
GHI     |   0 |   0 |   2
JKL     |   2 |   2 |   0
Total   |   2 |   3 |   2

NB: few changes need to be done in the declaration of the table as well, see the db fiddle.

score 1 · Accepted Answer · 2019-10-26 16:18:19Z

You can do this using conditional aggregation. By putting the base query into a common table expression, you also don't need to repeat the WHERE clause:

with base as (
  select company,  
         count(*) filter (where extract(month from my_date) = 1) as jan,
         count(*) filter (where extract(month from my_date) = 2) as feb,
         count(*) filter (where extract(month from my_date) = 3) as mar
  FROM mysql_test
  WHERE status = 'event' 
    AND my_date >= DATE '2019-01-01' AND my_date < DATE '2020-10-01'
  GROUP BY company
)
select *
from base
union all
select 'Total',
       count(*) filter (where jan > 0),
       count(*) filter (where feb > 0),
       count(*) filter (where mar > 0)
from base;

Using your sample data, this returns:

company | jan | feb | mar
--------+-----+-----+----
ABC     |   2 |   2 |   0
DEF     |   0 |   2 |   2
GHI     |   0 |   0 |   2
JKL     |   2 |   2 |   0
Total   |   2 |   3 |   2

Online example

Collectives™ on Stack Overflow

Need to convert MySQL query to PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related