How to do a Postgresql subquery in select clause with join in from clause

Question

I have table one user and other routine_streak i have to get username from another table using user_id I want use join to get username from user table in routine_streak which I have user_id in postgresql query...

Here is my Query:

select date(datetime) as dates,
        user_id,
        sum(CASE When status='completed' Then 1 Else 0 End ) as completed,
        sum(CASE When status='incompleted' Then 1 Else 0 End ) as incompleted,
        sum(CASE When status!='' Then 1 Else 0 End ) as total 
from routine_streak 
where user_id ='"+user_id+"' 
and datetime  between '"+start+"' and '"+end+"' 
group by user_id,date(datetime)

output should :

user_id:11,
user name:abc,
dates: 2019-01-30,
completed: 1
incompleted: 1,
total: 2

table name:routine_streak

CREATE TABLE IF NOT EXISTS routine_streak(
    id integer DEFAULT nextval('routine_streak_id_seq'::regclass) NOT NULL,
    user_id integer NOT NULL,
    wayid integer NOT NULL,
    status character varying(50)  DEFAULT NULL::character varying NULL,
    day character varying(50)  DEFAULT NULL::character varying NULL,
    streak character varying(50)  DEFAULT NULL::character varying NULL,
    datetime timestamp without time zone NULL,
    CONSTRAINT "routine_streak_pkey" PRIMARY KEY (id)
 );

COMMENT ON TABLE public.routine_streak IS 'null';

Second Table: user

CREATE TABLE IF NOT EXISTS user(
    id integer DEFAULT nextval('user_id_seq'::regclass) NOT NULL,
    cognito_identity_id character varying NOT NULL,
    username character varying NOT NULL,
    email character varying NULL,
    phone_number character varying NULL,
    name character varying NULL,

    password character varying(15)  NULL,
    status character(50)  DEFAULT NULL::bpchar NULL,
    CONSTRAINT "profile_pkey" PRIMARY KEY (id)
 );

There's a lot of mentions of doing a JOIN but you didn't do a JOIN anywhere. We don't have your table structure, or data, so it's hard to say. Have you tried a JOIN as you have suggested, though? — Jacob H, Feb 12 '19 at 12:43
Again, we don't have your table structure. I *assume* you need to join on user_id. Start here: https://stackoverflow.com/help/mcve — Jacob H, Feb 12 '19 at 12:46
Perfect. Please edit your post and add it in case I am not the one to respond next: https://stackoverflow.com/posts/54650303/edit — Jacob H, Feb 12 '19 at 13:12
`user` is a horrible name for a table because it's a reserved keyword and a built-in function: https://stackoverflow.com/questions/22256124 — , Feb 12 '19 at 13:32

score 0 · Answer 1 · edited Feb 12 '19 at 13:31

Try below query :-

select date(rs.datetime) as dates,
        rs.user_id,
        us.username,
        sum(CASE When rs.status='completed' Then 1 Else 0 End ) as completed,
        sum(CASE When rs.status='incompleted' Then 1 Else 0 End ) as incompleted,
        sum(CASE When rs.status!='' Then 1 Else 0 End ) as total 
from routine_streak rs
join "user" us on rs.user_id = us.id
where rs.user_id ='"+user_id+"' 
and rs.datetime  between '"+start+"' and '"+end+"' 
group by rs.user_id,us.username,date(rs.datetime)

How to do a Postgresql subquery in select clause with join in from clause

1 Answers1