I want to calculate the total gross amount of a CLIENT in all stores and in a specific store both in one query. Not repeating the same query twice as I did below and also not using group_by.
*Simply, I want to set a WHERE condition for just one of "SELECTS"*
My tables are: Clients and Orders
Clients Orders id name passport id client_id store_id gross_amount 1 Alex xxx 100 1 50 1000 2 Scott zzz 101 2 51 500
Expected result:
all_store my_store 1500 500
My query is
SELECT all_store.gross_amount, my_store.gross_amount FROM (SELECT SUM(orders.gross_amount) gross_amount FROM clients JOIN orders ON clients.id = orders.client_id WHERE clients.passport = 'xxx' AND FROM_UNIXTIME(orders.date_time) >= NOW() - INTERVAL 1 year) as all_store, (SELECT SUM(orders.gross_amount) gross_amount FROM clients JOIN orders ON clients.id = orders.client_id WHERE clients.passport = 'xxx' AND FROM_UNIXTIME(orders.date_time) >= NOW() - INTERVAL 1 year AND clients.store_id = '51') as my_store
Advertisement
Answer
SUM
with CASE WHEN
for given store to get gross amount for a specific store
SELECT SUM(orders.gross_amount) all_store, SUM(CASE WHEN clients.store_id = '51' THEN orders.gross_amount ELSE 0 END) my_store FROM clients JOIN orders ON clients.id = orders.client_id WHERE clients.passport = 'xxx' AND FROM_UNIXTIME(orders.date_time) >= NOW() - INTERVAL 1 year