skip to Main Content

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

2

Answers


  1. 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. 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