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

Adding a generated column with group by and count in postgresql

I have table which has store number and Fruits in it like below

Store Number fruit
111 Apple
121 Orange
111 Pinable
132 Apple
145 Grapes
121 Apple
111 Apple

I want to to add a generated column "Number of Apples", which tells me how many apples are there in each store. Resultant table would look like this

enter image description here

I am a beginner to PostgreSQL. Help me if anybody knows how to add this generated column in PostgreSQL

Tags:

2

Answers


  1. 0

    You can get what you are asking for by using a subquery in your query:

    select "Store Number",
       "Fruit", 
       (select count(*) 
        from store_fruit
        where "Store Number" = sf."Store Number" 
              and "Fruit" = 'Apple') as "Number of Apples"
from store_fruit sf;
    Login or Signup to reply.
  2. 0

    You can use the window function COUNT() and CASE clause to performs a conditional count of the Apple fruits for each store number :

    select store_number, fruit, 
       count(case when fruit = 'Apple' then 1 end) over (partition by store_number) as "Number of Apples"
from mytable

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search