2 Groupby in a Mysql query

Question

In Mysql I have a login table with the field day and User ID:

28-01-2019 - a
28-01-2019 - a
29-01-2019 - a
29-01-2019 - b
29-01-2019 - b
30-01-2019 - a
30-01-2019 - b
30-01-2019 - c

What I need is to give two Group By and have the following sum:

• How many users logged in per day (eg in the case of the 28th would be 1 and on the 30th would be 3)

It is possible with a single query?

Answer 1

In this case you must use the clause DISTINCT:

SELECT t.dia,
       COUNT(DISTINCT t.usuario) AS quantidade
  FROM tabela t
 GROUP BY t.dia
 ORDER BY t.dia

---

GROUP BY Modifiers

The GROUP BY clause Permits a WITH ROLLUP Modifier that causes Summary output to include extra Rows that represent Higher-level (that is, super-Aggregate) Summary Operations. ROLLUP Thus Enables you to Answer questions at Multiple levels of analysis with a single query. For example, ROLLUP can be used to provide support for OLAP (Online Analytical Processing) Operations.

Suppose that a Sales table has year, country, product, and Profit Columns for Recording Sales profitability:

CREATE TABLE sales(
  year    INT,
  country VARCHAR(20),
  product VARCHAR(32),
  profit  INT
);

To Summarize table Contents per year, use a simple GROUP BY like this:

SELECT year, SUM(profit) AS profit
  FROM sales
 GROUP BY year;

+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

In free translation:

The clause GROUP BY allows a modifierWITH ROLLUP which causes the summary output to include extra lines representing top-level summary operations (i.e., super-aggregated). The ROLLUP allows you to answer questions at multiple levels of analysis with a single query. For example, ROLLUP can be used to provide support for operationsOLAP (Online Analytical Processing).

Suppose a sales chart has year, country, product and profit columns to record sales profitability:

CREATE TABLE sales(
  year    INT,
  country VARCHAR(20),
  product VARCHAR(32),
  profit  INT
);

To summarize table content per year, use a GROUP BY plain as that:

SELECT year, SUM(profit) AS profit
  FROM sales
 GROUP BY year;

+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

---

You can check the execution on SQL Fiddle.