Subquery in Select Statement

Subquery in Select statement

; Select is the part showing the results. Subquery is used in Select statement when you want to add another field to an existing field.

Form: select 'field1', 'field2', (subquery) from ...

Q. Show how many or less likes if someone get compared to the average number of likes they've got.

1. Have a look at the table and take a sample data.

   select * from checkins
   

   SELECT avg(likes) from checkins
   where user_id = '4b8a10e6'
   

   

2. Make a structure of the data

   SELECT c.checkin_id,
               c.user_id,
               c.likes
     from checkins
   

   

3. We need to add one more field which indicates the average to the right of the table. Add the sample field to select.

   SELECT checkin_id,
       user_id,
       likes,
       ()
   from checkins
   

   SELECT checkin_id,
       user_id,
       likes,
       (
        SELECT avg(likes) from checkins
        where user_id = '4b8a10e6'
        )
   from checkins
   

   

SELECT c.checkin_id,
       c.user_id,
       c.likes,
       (
        SELECT avg(likes) from checkins
        where user_id = '4b8a10e6'
        )
  from checkins c

it works for that user_id. Now we want it shows the user info based on each user_id in user_id column.

SELECT c.checkin_id,
       c.user_id,
       c.likes,
       (
        SELECT avg(likes) from checkins
        where user_id = c.user_id
        )
  from checkins c

SELECT c.checkin_id,
       c.user_id,
       c.likes,
       (
        SELECT avg(likes) from checkins
        where user_id = c.user_id
        ) as avg_likes_user
  from checkins c