Count equal dates select Mysql

Question

Hello, in a Mysql database I have a table like this:

email | data_envio
  a   |2016-10-01 05:32:57
  b   |2016-10-02 09:36:56
  c   |2016-10-02 08:16:52
  d   |2016-10-03 10:36:51
  e   |2016-10-04 10:36:51

How do a select that returns me for example the amount of shipments on the day 02/10/2016?

Answer 1

One option is to search for the hours between 00 hours and 23:59:

SELECT COUNT(email) AS quantidade
  FROM tabela
 WHERE data_envio BETWEEN '2016-10-02 00:00:00' AND '2016-10-02 23:59:59';

You can also use the function DATE to convert to data ignoring the timetable information:

SELECT DATE(data_envio) as data_envio,
       COUNT(email) AS quantidade
  FROM tabela
 GROUP BY DATE(data_envio)

---

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 |
+------+--------+