SELECT First_name,last_name,email,ROUND (AVG(Amount),2) AS moneyspent FROM Customer INNER JOIN Payment ON Customer.customer_id= Payment.customer_id WHERE Staff_id=2 GROUP BY First_name,last_name,email HAVING SUM(Amount)>100 ORDER BY Moneyspent DESC first_name last_name email moneyspent Brittany Riley [email protected] 5.83 Arnold Havens [email protected] 5.14 Eleanor Hunt [email protected] 5.04…
The dataset is like this: g1 = {d1,d3,d5} g2 = {d1,d3,d2} g3 = {d4,d3,d5} I have the following table called director_genre: director_id | genre d1 g1 d1 g2 d2 g2 d3 g1 d3 g2 d3 g3 d4 g3 d5 g1…
I want to set variable either true or false based on Status column values, if any value is coming Cancel in Status column then I want to set variable as false OR want to set as true. I have query…
In a trading platform, users have three different actions: LOGIN, SEARCH, ORDER. Abandoned SEARCH action is defined as: customers LOGIN then do some SEARCH and do not ORDER before next LOGIN. Now I have a table: action_table, recording customer_id, action,…
I am trying to find out unique rows from the given data In my case I am storing buy sell of the stock in stock table. So same client can buy sell same stock. So ask is to just fetch…
I have a table like this one | two | three | code | four -----+-------+-------+---------+-------- 0 | 21750 | 10110 | 1016916 | 20.0 0 | 21750 | 10111 | 1017949 | 20.2 0 | 21750 | 10115 |…
DATE ACCOUNT VALUE 2023-06-06 1234 100 2023-06-07 1234 120 2023-06-08 1234 80 2023-06-06 3456 40 2023-06-07 3456 60 2023-06-08 3456 80 2023-06-05 5648 600 2023-06-06 5648 800 2023-06-06 5648 650 2023-06-07 5648 0 2023-06-08 5648 0 I'm passing current date…
SELECT v.accounting_date, v.payment_date FROM (SELECT doc.document_id, max(CASE WHEN doc.name='accountingDate' THEN doc.value END) AS accounting_date, max(CASE WHEN doc.name='paymentDate' THEN doc.value END) AS payment_date FROM go_appr_doc_variables doc GROUP BY doc.document_id) v WHERE TO_CHAR(TO_DATE(v.payment_date, 'YYYYMMDD')+8,'yyyymmdd') >= TO_CHAR(NOW(),'yyyymmdd') v.accounting_date and payment_date values format is…
I have some NativeQuery in my code which looks like this and where "stateValue" will be calculated and can be 1 or 0: SELECT MAX(CASE WHEN(? < t.someDate + interval '3 months' ) THEN 1 ELSE 0 END) AS stateValue…
I'm trying to come up with a good database design for this use case I have a table called expenses as follows id category_id item_id Problem item_id column wouldn't make sense if the expenses category is i.e. "Rent" It would…