0

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:

Y19 27
Y18 26
y17 25
y16 23
y20 19

Any idea how to accomplish that?

anvd
  • 3,997
  • 19
  • 65
  • 126

3 Answers3

1

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

nbk
  • 45,398
  • 8
  • 30
  • 47
  • well, three structure of this external data is just-> line: event name, column: year attendance. – anvd Mar 27 '22 at 22:38
  • 1
    Then use the selects to generate your count and union them as shown,cte is of course also a possibilty – nbk Mar 27 '22 at 22:40
1

You can "unpivot" 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, e.y16)
   , (17, e.y17)
   , (18, e.y18)
   , (19, e.y19)
   , (20, e.y20)
   ) t(year, count)
ORDER  BY count DESC;  -- your desired sort order

db<>fiddle here

Since this only needs a single scan over the table, it's many times faster than aggregating ever output value separately.

Each line in the VALUES expression forms a row with two columns: year (number defaults to integer) and count (type of referenced column).

See:

About LATERAL subqueries:

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 ...

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • @shawnt00: Not sure I understand your question. The lateral subquery can reference columns from all table expressions to the left in the same `FROM` clause. I clarified a bit. The added links should explain some more. – Erwin Brandstetter Mar 27 '22 at 23:37
  • Sorry I just misread and overlooked the whole subquery/derived table entirely. I thought you were referring to columns that didn't exist yet. – shawnt00 Mar 27 '22 at 23:43
-1

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
Pumba
  • 82
  • 2
  • You probably mean `ORDER BY`. But that still wouldn't address the question. – Erwin Brandstetter Mar 28 '22 at 01:08
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 28 '22 at 01:18