CODEWITHSUNDEEP
postgresqlgroupingcounting
Sahand
Sahand

Reputation: 8360

Grouping by attributes and counting, postgreSQL

I have written the following code that counts how many instances of each book_id there are in the table soldBooks.

    SELECT book_id, sum(counter) AS no_of_books_sold, sum(retail_price) AS generated_revenue
    FROM(
        SELECT  book_id,1 AS counter, retail_price
        FROM shipments 
        LEFT JOIN editions ON (shipments.isbn = editions.isbn)
        LEFT JOIN stock ON (shipments.isbn = stock.isbn)
        ) AS soldBooks
    GROUP BY book_id

As you can see, I used a "counter" in order to solve my problem. But I am sure there must be a better, more built in way of achieving the same result! There must be some way to group a table together by a given attribute, and to create a new column displaying the count of EACH attribute. Can somebody share this with me?

Thanks!

Upvotes: 0

Views: 34

Answers (1)

Charlie Rode
Charlie Rode

Reputation: 186

SELECT book_id, 
       COUNT(book_id) AS no_books_sold, 
       SUM(retail_price) AS gen_rev
  FROM shipments 
         JOIN editions ON (shipments.isbn=editions.isbn)
         JOIN stock ON (shipments.isbn=stock.isbn)
 GROUP BY book_id

Upvotes: 1

Related Questions