sql - Simple psql count query -

i new postgresql , generate summary data our table

we have simple message board - table name messages has element ctg_uid. each ctg_uid corresponds category name in table categories.

here categories select * categories order 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 | 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 messages on our messages count frequency of each category

i can on category category basis select count(msg_ctg_uid) messages msg_ctg_uid='13';

however possible in 1 liner?

the following gives the category , ctg_uid each message select ctg_category, msg_ctg_uid messages inner join categories on (ctg_uid = msg_ctg_uid);

but select ctg_category, count(msg_ctg_uid) messages inner join categories on (ctg_uid = msg_ctg_uid);

gives me error error: column "categories.ctg_category" must appear in group clause or used in aggregate function

how aggregate frequency of each category ?

you're missing group clause:

select ctg_category, count(msg_ctg_uid)  messages inner join categories on (ctg_uid = msg_ctg_uid); group ctg_category 

this means want count per ctg_category