PostgreSQL Select on multiple columns

Question

I have to make a query which will show used numbers and used times on few columns of integer type.

For this purpose I make a small example table with code suitable to paste into pgAdmin's sql editor:

DROP TABLE IF EXISTS mynums;
CREATE TABLE mynums 
   (rowindex serial primary key, mydate timestamp, num1 integer, num2 integer, num3 integer);

INSERT INTO mynums (rowindex, mydate, num1, num2, num3)
VALUES (1,  '2015-03-09 07:12:45', 1, 2, 3),
       (2,  '2015-03-09 07:17:12', 4, 5, 2),
       (3,  '2015-03-09 07:22:43', 1, 2, 4),
       (4,  '2015-03-09 07:25:15', 3, 4, 5),
       (5,  '2015-03-09 07:41:46', 2, 5, 4),
       (6,  '2015-03-09 07:42:05', 1, 4, 5),
       (7,  '2015-03-09 07:45:16', 4, 1, 2),
       (9,  '2015-03-09 07:48:38', 5, 2, 3),
       (10, '2015-03-09 08:15:44', 2, 3, 4);

Please help to build a query which would give results of used numbers and used times in columns num1, num2 and num3 together ordered by used times.

Result should be:

number  times
2       7
4       7
1       4
3       4
5       5

user330315 · Accepted Answer · 2015-03-18 10:41:08Z

2

You need to turn your columns into rows in order to be able to aggregate them:

select number, count(*)
from (
  select num1 as number
  from mynums
  union all 
  select num2
  from mynums
  union all 
  select num3
  from mynums
) t
group by number
order by number;

In general, having columns like num1, num2, num3 is a sign of a questionable database design. What happens if you need to add more numbers? It's better to create a one-to-many relationship and store the numbers associated with a rowindex in a separate table.

answered Mar 18, 2015 at 10:41

user330315

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

2 Comments

Frank Heikens Over a year ago

"questionable database design", my 2cents as well :-)

Wine Too Over a year ago

Thanks a lot "a name", work as expected. In this case fixed number of columns is acceptable now but for similar cases I would keep your advice in mind.

murison · Accepted Answer · 2015-03-18 10:46:11Z

1

this would work:

select number, count(*) as times
FROM (
  select rowindex, mydate, num1 as number FROM mynums
  UNION ALL 
  select rowindex, mydate, num2 FROM mynums
  UNION ALL 
  select rowindex, mydate, num3 FROM mynums
) as src
 group by number
 order by count(*) desc, number

http://sqlfiddle.com/#!15/cb1a7/3

answered Mar 18, 2015 at 10:46

murison

4,0552 gold badges26 silver badges44 bronze badges

1 Comment

Wine Too Over a year ago

Thanks murison, your solution also work but "a name" was faster.

Collectives™ on Stack Overflow

PostgreSQL Select on multiple columns

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related