SQLPLUS Select a Column and Count in the same SELECT statement

Question

I have this simple library checkout table created with the following columns and values inserted into it:

insert into CHECKOUT (MemID, Cat#, DateChkOut, DateDue, DateRet) 
    values (4, 'T 430.98 1956', '15-Mar-2011', '14-Jun-2011', '31-May-2011');
insert into CHECKOUT (MemID, Cat#, DateChkOut, DateDue, DateRet) 
    values (12, 'B 125.2 2013', '15-Mar-2011', '14-Jun-2011', NULL);
insert into CHECKOUT (MemID, Cat#, DateChkOut, DateDue, DateRet) 
    values (4, 'T 430.98 1956', '27-Dec-2012', '25-Mar-2013', NULL);

For each member, I need to list member id and the number of books ever checked out. I tried using:

select distinct MemID, count(distinct Cat#) from CHECKOUT;

but receive the error: not a single-group group function. Apparently I can't select a column and a count to display at the same time, but I could be wrong. Any ideas?

Gordon gave you a correct answer. The part that you didn't know about was pretty fundamental. That being the case, I've heard good things about the book, Teach Yourself SQL in 10 Minutes. — Dan Bracuk
– Dan Bracuk, Commented Apr 23, 2013 at 16:04

Gordon Linoff · Accepted Answer · 2013-04-23 17:11:49Z

2

You need a group by statement, not a select distinct:

select MemID, count(distinct Cat#)
from CHECKOUT
group by MemId

If you want members who never checked out a book, then you need a left outer join:

select memID, count(distinct Cat#)
from Members m left outer join
     Checkout co
group by m.MemId

The idea behind the left outer join is to keep all the rows in the first table, along with the matches in the second table. This allows you to find members who have no matching rows.

It seems like you are learning SQL. You should do some studying to better understand the language.

edited Apr 23, 2013 at 17:11

answered Apr 23, 2013 at 15:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user1687406 Over a year ago

Thank you so much for the help Gordon! I also need to list those members’ names who never checked out a book (their IDs and Names are stored in a separate table called Member and ID is a PK of checkout's MemID). Is the group by function part of that as well, or is there a different function that is useful to figure that out?

Collectives™ on Stack Overflow

SQLPLUS Select a Column and Count in the same SELECT statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related