How to join 2 SQL queries into a single one (one is a mean value calculation and another search criteria with BETWEEN

Question

I’m setting up a system that finds providers in a specific region, calculates the average value charged by them and brings this value.

But before that he will only pick up the providers that are available on the requested date. I have these 2 consultations made and separated, but I’m not getting a way to put them both together in one.

Consultation of the availability rule:

SELECT *
FROM fcs_prestadores_pedidos
WHERE hora_entrada not BETWEEN '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'
AND hora_saida not between '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'

Query with calculation of the average value within the radius of 90km

SELECT avg(valor)
FROM (SELECT valor,(6371 * acos( cos( radians(-23.5427450) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-46.6513010) ) + sin( radians(-23.5427450) ) * sin( radians( lat ))))
AS distancia
FROM fcs_prestadores) virtual
WHERE distancia < 90

How can I make it first to make the availability query, then calculate the average value?

Answer 1

Use the clause NOT EXISTS in the second query using the values of the first:

SELECT AVG(virtual.valor) AS valor,
       virtual.id
  FROM (SELECT fp.valor,
               (6371 * ACOS(COS(RADIANS(-23.5427450)) * COS(RADIANS(fp.lat)) * COS(RADIANS(fp.lng) - RADIANS(-46.6513010)) + SIN(RADIANS(-23.5427450)) * SIN(RADIANS(fp.lat)))) AS distancia,
               fp.id
          FROM fcs_prestadores fp) virtual
 WHERE virtual.distancia < 90
   AND NOT EXISTS(SELECT 1
                    FROM fcs_prestadores_pedidos fpp
                   WHERE fpp.prestador_id = virtual.id
                     AND (fpp.hora_entrada BETWEEN '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'
                      OR fpp.hora_saida BETWEEN '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'))
 GROUP BY virtual.id

The changes made to the query were:

• Added the id on the return of the table fcs_prestadores to be compared with fcs_prestadores_pedidos;
• GROUP BY for id of the provider to ensure that one line will be shown for each;
• NOT EXISTS check if there is a request for the provider determined in the desired range. The link is made by the column id.

---

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