Question 89845 in PostgreSQL Question posted in
The official documentation can be found here.

What's the best way to group SQL results by items batch? – Postgresql

For example, I have a simple table books:

author book
Author-A Book-A1
Author-A Book-A2
Author-B Book-B1
Author-C Book-C1
Author-C Book-C2

And I need to count books by each author, so I’ll write:

select author, count(*) from books
group by author

# Author-A = 2
# Author-B = 1
# Author-C = 2

But now I need to count books by groups of authors:

groupA = ['Author-A', 'Author-C'],
groupB = ['Author-B']

select authorGroup, count(*) from books
group by {
  case author in groupA -> 'groupA'
  case author in groupB -> 'groupB'
} as authorGroup

# ['Author-A', 'Author-C'] = 4
# ['Author-B'] = 1

These groups can be different and come from another module.
What’s the best way to write this requests? Maybe without union such as:

select author as 'groupA', count(*) from books
where author in { groupA }
  union
select author as 'groupB', count(*) from books
where author in { groupB }

because there could be a lot of groups in request (~20-30)

The problem is that these groups can be absolutely dynamic: I can request ['Author-A', 'Author-B'] in one request as one group and ['Author-B', 'Author-C'] in another.

For example, the group is not something like author’s country or genre. It can be totally dynamic.

Tags:

2

Answers


  1. 0

    The usual way is to JOIN on to a mapping table, which can be an in-line-view if need be (though I recommend an actual table, which can be indexed).

    WITH
  author_group AS
(
  SELECT 'Author-A' AS author, 'Group-A' AS group_label
  UNION ALL
  SELECT 'Author-B' AS author, 'Group-B' AS group_label
  UNION ALL
  SELECT 'Author-C' AS author, 'Group-A' AS group_label
)
SELECT
  author_group.group_label,
  COUNT(*)
FROM
  books
INNER JOIN
  author_group
    ON author_group.author = books.author
GROUP BY
  author_group.group_label

    Similar results can be achieved with CASE expressions, but it doesn’t scale very well…

    WITH
  mapped_author AS
(
  SELECT
    *,
    CASE author
      WHEN 'Author-A' THEN 'Group-A'
      WHEN 'Author-B' THEN 'Group-B'
      WHEN 'Author-C' THEN 'Group-A'
    END
      AS author_group
  FROM
    books
)
SELECT
  author_group,
  COUNT(*)
FROM
  mapped_author
GROUP BY
  author_group
    Login or Signup to reply.
  2. 0

    First you need to create a new table that show in what group is the author.

    Later you just count

    Like this:

    select distinct a.group_auth, count(a.book) over (partition by a.group_auth) 
from
(select 
       case when b.Author in [groupA] then 'groupA', 
             when b.Author  in [groupB] then 'groupB' 
        end case as group_auth,
        b.book as book
from books b
) as a
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search