Mysql where condition for single SELECT [not all]

November 12, 2021

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

>Solution :

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