I want to filter in subquery with aliased field from main query: select a.created_at::date as creation_date, a.column as col1, (select count(*) from table b where a.col1 = b.col1 and a.created_at::date = b.created_at::date) as count_one from table a group by creation_date,…
I am using MySQL and I would like a field in my table to be periodically incremented per month. I'm making a banking application and would like to give an interest rate for all the accounts... can anyone please advice…
I have a table where the same ID can have multiple entries with start time and end time. How would I calculate the total time spent by the ID. The result I would want for the table below would be…
I have troubles creating certain aggregations. I'm using this table in Postgres: CREATE TABLE public.customer_courier_chat_messages ( sender_app_type character varying(255), customer_id integer, from_id integer, to_id integer, chat_started_by_message boolean, order_id integer, order_stage character varying(255), courier_id integer, message_sent_time timestamp with time zone );…
I have the following in tables in PostgreSQL: Relationship: ID: int Status: string RelatedParty: RelationshipID: int PersonID: int Role: string The idea is that a relationship has two related parties, each of which has a role in the relationship. Only…
In the query I shared below, I use 3 recursive loops to cumulatively retrieve debit, credit and budget values. Can I do this using 1 recursion instead of 3 recursions? Also, can it be done in this format without changing…
I am using Postgres, and would like to write SQL to do the following. I have this so far: select a.id, string_agg(concat(c.fname), ', ') as approvers from autoapprovalapproverconfig a left join autoapprovalapproverconfigmandate a2 on a2.autoapprovalaconfigurationid = a.id left join person…
we have a database table where we store a new record every 30 minutes. The data looks like this: I wrote the following query: SELECT * FROM datastore_usage WHERE datastore_id = 1 GROUP BY HOUR(created_at) ORDER BY created_at DESC LIMIT…
Hello everyone i am developing a website that listing and filtering products. My problem is products table is have 40000 row and product_details have 2.1 million row. I want to filter products table with product_details table my code is $sliced…
I wrote this query to show me the last purchase of each customers using "EXISTS" but it is not working. it returns all of the rows without filtering them. Can you please advise me how to resolve this issue? Thank…