current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite

Using PostgreSQL 9.4.1, I am trying to identify/display the occurrences of values over 3 different columns. See below (apologies for the formatting, I can't get a proper table format. Type, type1 and type2 are the column names.

The table name is 'documents'

Type   /    Type1    /    Type2

USA /   China / Africa    
China / USA / Chemicals  
Chemicals / Africa / USA

Below is \d+ of the table:

     Column     |  Type  |                       Modifiers                        
----------------+--------+--------------------------------------------------------
 id             | bigint | not null default nextval('documents_id_seq'::regclass)
 title          | text   | 
 description    | text   | 
 source         | text   | 
 url            | text   | 
 emaillink      | text   | 
 emailurl       | text   | 
 type           | text   | 
 language       | text   | 
 author         | text   | 
 publisheddate  | date   | default ('now'::text)::date
 comments       | text   | 
 classification | text   | 
 submittedby    | text   | 
 localurl       | text   | 
 type1          | text   | 
 type2          | text   | 
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)

I would like a query that returns:

Africa - 2
Chemicals - 2
China - 2
USA - 3

This is a query likely to get run fairly liberally, so I'd like to avoid expensive queries if at all possible.

Any help would be greatly appreciated!

Thanks.

share|improve this question
up vote 1 down vote accepted

You can use union all to pivot the columns into rows and then do a group by to count the occurrences for each type

select type, count(*) from (
    select type1 as type from mytable
    union all select type2 from mytable
    union all select type3 from mytable
) t1 group by type
share|improve this answer
    
Thanks so much! I only have about 1000 records, so I couldn't easily discern a difference in speed, but went with the full join anyways. – Brooks May 7 '15 at 3:19
    
How would I sort the full join? – Brooks May 7 '15 at 3:21
    
FYI, the full join doesn't actually merge the three columns for some reason. The output appears to output each column individually. I saw duplicates. – Brooks May 7 '15 at 3:29
up vote 1 down vote

Try this:

SELECT WORD, COUNT(1) OCCURENCES
FROM (
    SELECT Type FROM TableName
    UNION ALL
    SELECT Type1 FROM TableName
    UNION ALL
    SELECT Type2 FROM TableName)
GROUP BY WORD;
share|improve this answer
1  
If I could select both as answers, I would, you were only what...2 minutes behind FuzzyTree? – Brooks May 7 '15 at 3:15
    
Great minds think alike ;) – bdunn May 7 '15 at 3:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.