Select to calculate all records and total record by one condition

Question

I have a sales table per user, which connects user_id, product_name and sales value.

I would like to make an inquiry to return how many products were sold per user and how many products with more than 1000 real were sold per user, in just one select.

SELECT
  sl.user_id,
  count(sl.user_id) as total_records,
  count(sl.sale_value) > 1000 as total_goal_achieved
FROM SALES sl
GROUP BY 1

I intend to create a temporary table with this data.

Answer 1

One solution is to make two queries and join them with a LEFT JOIN. See example:

CREATE DATABASE example;
use example;
CREATE TABLE sales (id INT, product_name VARCHAR(100), VALOR INT);
INSERT INTO sales (id,product_name,valor)
VALUES (1,'CAFÉ',1200);
INSERT INTO sales (id,product_name,valor)
VALUES (2,'cerveja',500);
INSERT INTO sales (id,product_name,valor)
VALUES (2,'macarrao',3000);
INSERT INTO sales (id,product_name,valor)
VALUES (3,'vinho',12500);
INSERT INTO sales (id,product_name,valor)
VALUES (4,'azeitona',250);

SELECT id, total, COALESCE(totalm1000,0) as totalm1000
    FROM
    (SELECT id, COUNT(DISTINCT(product_name)) as total
    FROM example.sales
    GROUP BY id) as T1
    LEFT JOIN
    (SELECT id as id2, COUNT(DISTINCT(product_name)) as totalm1000
    FROM example.sales
    WHERE valor>1000
    GROUP BY id2) as T2
    ON id=id2

Returns:

+------+-------+------------+
| id   | total | totalm1000 |
+------+-------+------------+
|    1 |     1 |          1 |
|    2 |     2 |          1 |
|    3 |     1 |          1 |
|    4 |     1 |          0 |
+------+-------+------------+

The first query returns the total products sold per id, the second returns the total products sold with value greater than 1000 per id.