SQL Basic: Group by function

I am trying to get a table that would outline two group by functions but having some minor difficulty. select to_char("CreateTime", ‘YYYY-MM’) as MonthYear, floor(sum("Time"))::integer / 60 as "#MinutesWorkouts", sum(case when "Type" = 29 then 1 else 0 end) as "#Streaming", (sum(case when "Type" = 29 then "Time" else 0 end))::integer / 60 as "StreamingMinutes",… Read More SQL Basic: Group by function

What is the benefit to use sequence rather than just insert and get generated ID in postgresql?

What is the benefit to use sequence rather than just insert and get generated ID in postgresql ? I mean why don’t just insert a record and the rdbms generate ID for you? >Solution : For most purposes, SERIAL does the same thing as a sequence can do. However, if you wanted your auto increment… Read More What is the benefit to use sequence rather than just insert and get generated ID in postgresql?

Confused on why I am getting a syntax error when writing my where clause?

I have to create two queries. In the first query I must select all the rows in Illinois, Indiana, Wisconsin, and Michigan. I am confused on why I am getting a syntax error when I try to write my select statement. Dealerships is the table being referenced. Can anyone please help? select * from dealerships… Read More Confused on why I am getting a syntax error when writing my where clause?

Can I do a SQL select on a subquery and also retrieve the subquery?

I have the following SQL: SELECT id, user_id, coordinates FROM fields WHERE id IN (SELECT field_id FROM transactions WHERE id IN (11,10,12)) There are 2 tables: transactions and fields. Both of them have their own id field. However, in transactions there’s also a field to connect each row to the fields.id called field_id. I have… Read More Can I do a SQL select on a subquery and also retrieve the subquery?

How to join two tables based on a calculated field?

I have two SQL queries that output the same kind of output and have the same grouping and order : select date_trunc(‘month’, inserted_at)::date as date, count(id) from payment_logs where payment_logs.event_name = ‘subscription_created’ group by date order by date desc; select date_trunc(‘month’, inserted_at)::date as date, count(id) from users group by date order by date desc; I… Read More How to join two tables based on a calculated field?