We help IT Professionals succeed at work.

Count duplicate rows

MK15
MK15 used Ask the Experts™
on
Hi,

How can i get all the duplicate rows to display as a single figure.

select b.book_title, b.isbn, pb.new_price, p.payment_amount, count(pb.quantity)
    from purchased_books as pb inner join book_info as b on b.book_id = pb.book_id
    inner join payment as p on p.payment_id = pb.payment_id
    inner join customer as c on c.customer_id = pb.customer_id


table
quantity should display 2 for isbn 1858818400 and 1 for isbn 067403063X


thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
if pb.quantity has valid values:

select b.book_title, b.isbn, pb.new_price, p.payment_amount, sum(pb.quantity)
    from purchased_books as pb inner join book_info as b on b.book_id = pb.book_id
    inner join payment as p on p.payment_id = pb.payment_id
    inner join customer as c on c.customer_id = pb.customer_id
group by b.book_title, b.isbn, pb.new_price, p.payment_amount

if not, and you just assume 1 row in the database means one item:

select b.book_title, b.isbn, pb.new_price, p.payment_amount, count(pb.preferably_primary_key_column)
    from purchased_books as pb inner join book_info as b on b.book_id = pb.book_id
    inner join payment as p on p.payment_id = pb.payment_id
    inner join customer as c on c.customer_id = pb.customer_id
group by b.book_title, b.isbn, pb.new_price, p.payment_amount

Author

Commented:
thank you very much, it did the trick :)