Sort events by highest occurrence by year-CodePudding

I am trying to find how many events occur by year. Currently I have this query, that basically counts when an event has visitors:

SELECT 
count(visitors_y_2016) as y_16,
count(visitors_y_2017) as y_17,
count(visitors_y_2018) as y_18,
count(visitors_y_2019) as y_19, 
count(visitors_y_2020) as y_20
FROM event
;

y16  y17  y18  y19  y20 
23   25   26   27   19

But what I am looking for is an order by the year with more events:

Any idea how to accomplish that?

CodePudding user response：

your table design looks quite strange, as such information should be in rows and not columns.

But you can UNION all results and then sort them

CREATE TABLE event (visitors_y_2016 int,visitors_y_2017 int,visitors_y_2018 int,visitors_y_2019 i

(SELECT 
'y_16' ,count(visitors_y_2016) as cnt
FROM event
UNION ALL
SELECT 
'y_17',count(visitors_y_2017)
FROM event
UNION ALL
SELECT 
'y_18',
count(visitors_y_2018) 
FROM event
UNION ALL
SELECT 
'y_19',
count(visitors_y_2019) 
FROM event
UNION ALL
SELECT 
'y_20',
count(visitors_y_2020) 
FROM event)
ORDER BY cnt
;

?column? | cnt
:------- | --:
y_16     |   0
y_17     |   0
y_18     |   0
y_19     |   0
y_20     |   0

db<>fiddle here

CodePudding user response：

You can "counter-pivot" with a VALUES expression in a LATERAL subquery:

SELECT t.*
FROM  (
   SELECT count(visitors_y_2016) AS y16
        , count(visitors_y_2017) AS y17
        , count(visitors_y_2018) AS y18
        , count(visitors_y_2019) AS y19 
        , count(visitors_y_2020) AS y20
   FROM   event
   ) e, LATERAL (
   VALUES
     ('16', y16)
   , ('17', y17)
   , ('18', y18)
   , ('19', y19)
   , ('20', y20)
   ) t(year, count);

db<>fiddle here

See:

SELECT DISTINCT on multiple columns

But your table design raises questions. Typically you'd have a single date or timestamp column visitors instead of visitors_y_2016, visitors_y_2017 etc. - and a simpler query based on that ...

CodePudding user response：

I don't think you need a select on each year. I don't exactly know your table, but there should be a better wayu to organize your data. Also, SORT BY should be your friend if you wanna sort data. You just gotta have a single SELECT to use it like for example:

SORT BY
VISITOR_COUNT