Different queries but return the values in the same table

Question

Different queries but return the values in the same table

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

I have two different queries, so I have two variables with the result of each query, but I wanted to show everything in the same table, how do I do it in pHP?

$sql = "SELECT Product , SUM(Amount) AS 'Total 2017' FROM dbcentro.Registo WHERE YEAR(RegistrationDate) = YEAR(NOW()) GROUP BY Product;";
$sql1 = "SELECT Produto, CAST(Total/3 AS DECIMAL(15,1)) AS 'Média 1º Trimestre' FROM(SELECT Product AS Produto, SUM(Amount) AS Total FROM dbcentro.Registo WHERE MONTH(RegistrationDate) BETWEEN 1 AND 3 AND YEAR(RegistrationDate) = YEAR(NOW()) GROUP BY Product) Soma;";

$result = mysqli_query($conn, $sql);
$result1 = mysqli_query($conn, $sql1);

What I wanted was for the column Average 1 quarter to appear here the result in the table as shown in the image below:

The first two columns are from the first query and now I want the result of the second query to appear in that column, but I still can not do that.

I solved the problem by creating a while within another while

php

asked by anonymous 07.08.2017 / 15:00

2 answers

1

Try to put the 1st Quarter average calculation in just one select:

SELECT 
    Product , 
    SUM(Amount) AS 'Total 2017',
    CAST(SUM(case when MONTH(RegistrationDate) BETWEEN 1 AND 3  then Amount else 0 end)/3 as DECIMAL(15,1)) as 'Média 1º Trimestre'
    FROM dbcentro.Registo 
    WHERE YEAR(RegistrationDate) = YEAR(NOW()) 
        GROUP BY Product;

07.08.2017 / 15:08

Libraries in python to check if a number is prime Bugging shortcuts with python

score 3 · Accepted Answer

Based on the SQL that Walmir passed you works! and also to do using "IF":

SELECT 
    Product,
    SUM(Amount) AS 'Total 2017',
    CAST(SUM(if(MONTH(RegistrationDate) BETWEEN 1 AND 3 , Amount, 0)) / 3  AS DECIMAL (15 , 1 )) AS 'Média 1º Trimestre'
FROM
    dbcentro.Registo
WHERE
    YEAR(RegistrationDate) = YEAR(NOW())
GROUP BY Product;