Simple psql count query

Question

I am very new to postgresql and would like to generate some summary data from our table

We have a simple message board - table name messages which has an element ctg_uid. Each ctg_uid corresponds to a category name in the table categories.

Here are the categories select * from categories ORDER by ctg_uid ASC;

 ctg_uid |    ctg_category    | ctg_creator_uid 
---------+--------------------+-----------------
       1 | general            |               1
       2 | faults             |               1
       3 | computing          |               1
       4 | teaching           |               2
       5 | QIS-FEEDBACK       |               3
       6 | QIS-PHYS-FEEDBACK  |               3
       7 | SOP-?-CHANGE       |               3
       8 | agenda items       |               7
      10 | Acq & Process      |               2
      12 | physics-jobs       |               3
      13 | Tech meeting items |              12
      16 | incident-forms     |               3
      17 | ERRORS             |               3
      19 | Files              |              10
      21 | QIS-CAR            |               3
      22 | doses              |               4
      24 | admin              |               3
      25 | audit              |               3
      26 | For Sale           |               4
      31 | URGENT-REPORTS     |               4
      34 | dt-jobs            |               3
      35 | JOBS               |               3
      36 | IN-PATIENTS        |               4
      37 | Ordering           |               4
      38 | dep-meetings       |               4
      39 | reporting          |               4

What I would like to do is for all messages on our messages is count the frequency of each category

I can do it on a category by category basis SELECT count(msg_ctg_uid) FROM messages where msg_ctg_uid='13';

However is it possible to do this in a one liner?

The following gives the the category and ctg_uid for each message SELECT ctg_category, msg_ctg_uid FROM messages INNER JOIN categories ON (ctg_uid = msg_ctg_uid);

but SELECT ctg_category, count(msg_ctg_uid) FROM messages INNER JOIN categories ON (ctg_uid = msg_ctg_uid);

gives me the error ERROR: column "categories.ctg_category" must appear in the GROUP BY clause or be used in an aggregate function

How do I aggregate the frequency of each category ?

Answer 1

You're missing the group by clause:

SELECT ctg_category, count(msg_ctg_uid) 
FROM messages INNER JOIN categories ON (ctg_uid = msg_ctg_uid);
GROUP BY ctg_category

this means you want the count per ctg_category