CODEWITHSUNDEEP
sqloracle-database
taathtwostep
taathtwostep

Reputation: 35

count function sql

The relational schema for the Academics database is as follows:

DEPARTMENT (deptnum, descrip, instname, deptname, state, postcode)
ACADEMIC (acnum, deptnum*, famname, givename, initials, title)
PAPER (panum, title)
AUTHOR (panum*, acnum*)
FIELD (fieldnum, id, title)
INTEREST (fieldnum*, acnum*, descrip)

For each academic, return the acnum, givename, famname and the total number of papers s/he has written.

Note that if an academic has not written any paper, his/her total should be zero.

You can use JOIN operators such as NATURAL, JOIN ...ON.

I can't seem to figure out this SQL query. The count area is where I'm having trouble. My code is below - I've been struggling with this for a few hours.

select
    acnum,
    givename,
    famname,
    count(panum)
from 
    academic 
natural join 
    author

Upvotes: 0

Views: 95

Answers (2)

Littlefoot
Littlefoot

Reputation: 142720

According to what you posted, this might be what you're looking for:

SQL> with
  2  -- sample data
  3  academic (acnum, famname, givename) as
  4    (select 1, 'Smith', 'John'   from dual union all
  5     select 2, 'Tiger', 'Smith'  from dual union all   --> no published papers
  6     select 3, 'Foot' , 'Little' from dual
  7    ),
  8  author (panum, acnum) as
  9    (select 100, 1 from dual union all
 10     select 200, 3 from dual
 11    )
 12  -- query you need begins here
 13  select a.acnum,
 14         a.famname,
 15         a.givename,
 16         count(t.panum)
 17  from academic a left join author t on a.acnum = t.acnum
 18  group by a.acnum,
 19           a.famname,
 20           a.givename
 21  order by a.acnum;

     ACNUM FAMNA GIVENA COUNT(T.PANUM)
---------- ----- ------ --------------
         1 Smith John                1
         2 Tiger Smith               0
         3 Foot  Little              1

SQL>

Error you got

ORA-01747: invalid user.table.column, table.column, or column specification

was caused by the count function's parameter. If you used a tool that actually shows where it (the error) happened, you'd see it yourself. For example, SQL*Plus is such a tool:

<snip>
 12  -- query you need begins here
 13  select a.acnum,
 14         a.famname,
 15         a.givename,
 16         count(t.*)
 17  from academic a left join author t on a.acnum = t.acnum
 18  group by a.acnum,
 19           a.famname,
 20           a.givename
 21  order by a.acnum;
       count(t.*)              --> this caused
               *               --> the error!
ERROR at line 16:
ORA-01747: invalid user.table.column, table.column, or column specification


SQL>

Upvotes: 1

zealous
zealous

Reputation: 7503

This one should work, you need a left join to get all academic and group by for aggregation.

select
    ac.acnum,
    givename,
    famname,
    coalesce(count(au.*), 0) as total_papers
from academic ac

left join author au
on ac.acnum = au.acnum

group by
    ac.acnum,
    givename,
    famname

Upvotes: 0

Related Questions