SQL query to return value based on value in row

Question

Is there a way to popullate a column based on another column.

For example

SELECT name, age, (select count(*) FROM ORDERS WHERE name = **value in column [name]**) FROM PEOPLE

Any help is greatly appreciated.

Yes but not with that syntax. Do you have some sample data? I would suggest joining the tables or using a subquery to get the solution — Nick
– Nick, Commented Jul 12, 2019 at 12:33

forpas · Accepted Answer · 2019-07-12 13:16:22Z

3

You can but you have to use proper aliases for the tables:

SELECT 
  p.name, 
  p.age, 
  (select count(*) FROM ORDERS o WHERE o.name = p.name)  counter
FROM PEOPLE p

so the 2 columns from different tables both having the same name name can be distinguished.

As suggested by Larnu, try this alternative which does the same by joining the tables and aggregating:

SELECT 
  p.name, 
  p.age, 
  count(o.name) counter
FROM PEOPLE p LEFT JOIN ORDERS o
ON o.name = p.name
GROUP BY p.name

edited Jul 12, 2019 at 13:16

answered Jul 12, 2019 at 12:32

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thom A Over a year ago

Wouldn't this be better served with a JOIN and aggregation? The subquery will likely result in multiple scans of ORDERS.

forpas Over a year ago

I'm not sure about it. There are cases where such embedded queries are more efficient. Anyway this answer is based on the OP's code. The explanation about the aliases I think is more useful than a point to another direction

Christopher Vickers Over a year ago

Thanks guys! You've been a massive help

Collectives™ on Stack Overflow

SQL query to return value based on value in row

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related