Still learning SQL/PostgreSQL. I am struggling to figure out how to stop returning an array with a null value as [null] and instead just return an empty array. COALESCE( json_agg( CASE WHEN socials.id IS NULL THEN NULL ELSE json_build_object('id', socials.id,…
I was actually trying some thing similar with a sub query, and couldn’t understand what was going wrong. I have managed to simplify the question to the following. I have a simple table which may include a NULL in one…
Table name: DocumentationBook id name_page 1 rule1 2 rule2 1 rule3 2 rule3 3 rule2 3 rule3 I have tried to do this, SELECT COUNT(id) FROM DocumentationBook where name_page = "rule2" OR name_page = "rule3" but it doesnt look right…
I have a table numbers: id n 1 1 2 2 3 5 4 7 5 9 And I have a table of rules: rule_type n LT 2 GT 7 GT 9 EQ 2 (LT - less-than, GT - greater-than,…
I have 2 MySQL tables, namely posts and votes The posts table contains articles published by authors (users). The votes table contains votes against the particular post by users. I'd like to get all posts that have or haven't been…
I have the following table called worker_log: log_date total_workers store 2021-01-01 2 Ralphs 2021-02-01 0 Ralphs 2021-03-01 3 Ralphs 2021-01-01 3 Benny's 2021-02-01 0 Benny's 2021-03-01 1 Benny's For Ralphs, from 1/1/21 to 1/31/21, there were 2 total workers. From…
I am loading CSV files to Redshift. I first run the COPY command to a staging table, then normalize the account data into a separate lookup table. BEGIN; INSERT INTO AccountLookup (Account) SELECT DISTINCT Account FROM Staging db WHERE NOT…
I am trying to solve an issue where I need to find the previous,current and next record where the query matches certain condition. In the example image below I am interested in only records where pattern_id=23587462 (can be multiple), and…
This my dataset and i want to find No. of gender name|gender| ----+------+ AA |Male | BB |Male | CC |Male | DD |Female| EE |Female| sql query : select gender,count(*) from schoolofit.user_request_1 group by gender result: gender|count| ------+-----+ Female|…
I have 2 tables made like so: NickName Points Player 1 15 Player 2 8 NickName Points Player 1 33 Player 2 22 and I need to get this table: NickName Points Player 1 48 Player 2 30 Is it…