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

Postgresql – How do I add this query to the column in my table?

I want to add this query to a column in my table.I am using pgadmin

select  customer_id,
         count(*)
from  summary
group by customer_id
order by customer_id asc

I got as far as

ALTER TABLE summary
add  sum_of_rentals INT

UPDATE SUMMARY
SET sum_of_rentals =
(
select count(*)
    FROM summary
    WHERE customer_id= customer_id
    GROUP BY customer_id
)

I tried this

 UPDATE SUMMARY
SET sum_of_rentals =
(
select count(*)
    FROM summary
    WHERE customer_id= customer_id
    GROUP BY customer_id
)

and got this answer from pgadmin

ERROR: more than one row returned by a subquery used as an expression

SQL state: 21000

Tags:

3

Answers


  1. 0

    in don’t know, what for you need that query.

    But as i said in the comment, you produce with your subquery a rowset with as many rows a you have customers, but postgres exoects a sclar value

    so one solution is to make a correlated subquery

    CREATE tABLE summary (customer_id int);

    CREATE TABLE

    INSERT INTO summary VALUEs (1),(1),(2),(3)

    INSERT 0 4

    ALTER TABLE summary
add  sum_of_rentals INT;



    ALTER TABLE

    UPDATE summary s1
SET sum_of_rentals =
(
select count(*)
    FROM summary
    WHERE customer_id= s1.customer_id

)

    UPDATE 4

    SELECT * FROM summary
    customer_id sum_of_rentals
    1 2
    1 2
    2 1
    3 1 
    SELECT 4

    fiddle

    Login or Signup to reply.
  2. 0

    If my understanding is correct, you want to calculate the total rentals for each customer and apply that total to every row associated with that particular customer in the table.

    UPDATE SUMMARY s
SET sum_of_rentals = t.sum_of_rentals
FROM SUMMARY su
INNER JOIN (
  SELECT customer_id, COUNT(*) AS sum_of_rentals 
  FROM SUMMARY
  GROUP BY customer_id
) AS t ON su.customer_id = t.customer_id
WHERE s.customer_id = su.customer_id

    Demo here

    Login or Signup to reply.
  3. 0 
    update summary
set sum_of_rentals = (
    select count(*)
    from summary
    where customer_id= customer_id
    group by customer_id
)

    In the subquery, customer_id= customer_id is referring to customer_id for the rows being selected. You’re querying rows whose customer_id equals their customer_id which is all rows so you get multiple results. You need to disambiguate them by using table aliases and fully qualified column names.

    update summary
set sum_of_rentals = (
    select count(*)
    from summary sub
    where summary.customer_id = sub.customer_id
    group by sub.customer_id
)

    Demonstration

    Note: you may wish to move statistical caching like this into a join table. Then if you have 1,000,000 rows but only 1,000 unique values you only have 1,000 rows to update.

    Demonstration.

    Regardless, you probably want to keep it up to date with an insert/update/delete trigger.

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

Back To Top
Search