I have 3 sql queries as follow with different conditions and rules but giving the same output:
Query1:
Select CONVERT(char(10), DatePayment, 120) PaymentDate, IsNull(SUM(UnitPrice * Quantity), 0) POSAmount From POSSales P Inner Join POSSalesDetails PD On P.ID = PD.SalesID Inner Join Payments PAY On P.PaymentID = PAY.ID Inner Join POSItems PDI On PD.ItemID = PDI.ID Where DatePayment >= '2019-04-01 08:00' And DatePayment <= '2019-04-30 08:00' group by CONVERT(char(10), DatePayment, 120) order by CONVERT(char(10), DatePayment, 120)
sample output
---------------------------- | PaymentDate | POSAmount| --------------------------- | 2019-05-01 | 510.00 | | 2019-05-02 | 120.00 | | ........ | ........ | ----------------------------
Query2:
Select CONVERT(char(10), DatePayment, 120) PaymentDate, IsNull(SUM(TotalAmount), 0) ShowerAmount From ShowerBookings S Inner Join ShowerPayments SP On S.ID = SP.BookingID Inner Join Payments PAY On SP.PaymentID = PAY.ID Where DatePayment >= '2019-04-01 08:00' And DatePayment <= '2019-04-30 08:00' group by CONVERT(char(10), DatePayment, 120) order by CONVERT(char(10), DatePayment, 120)
sample output
---------------------------- | PaymentDate |ShowerAmount| ---------------------------- | 2019-05-01 | 220.00 | | 2019-05-02 | 310.00 | | ........ | ........ | -----------------------------
Query3:
Select CONVERT(char(10), DatePayment, 120) PaymentDate, IsNull(SUM(TotalAmount), 0) LockerAmount From LockerBookings L Inner Join LockerPayments LP On L.ID = LP.BookingID Inner Join Payments PAY On LP.PaymentID = PAY.ID Where DatePayment >= '2019-04-01 08:00' And DatePayment <= '2019-04-30 08:00' group by CONVERT(char(10), DatePayment, 120) order by CONVERT(char(10), DatePayment, 120)
sample output
---------------------------- | PaymentDate |LockerAmount| ---------------------------- | 2019-05-01 | 150.00 | | 2019-05-02 | 180.00 | | ........ | ........ | -----------------------------
and my final output.. I would like to sum(Amount1+Amount2+Amount3) and group by PaymentDate.
I’ve tried the following but it doesn’t work
select Q1.PaymentDate, sum(Q1.Amount1 + Q2.Amount2 + Q3.Amount3) as TotalAmount from (Query1) as Q1, (Query2) as Q2, (Query3) as Q3
and I am expecting results like this:
expected output
---------------------------- | PaymentDate | TAmount | --------------------------- | 2019-05-01 | 880.00 | | 2019-05-02 | 610.00 | | ........ | ........ | ----------------------------
Life would be much more easier if it can work that way -_- Appreciate your kind help and assistant. Thanks
NOTE I update the query for better understanding as for why I need to all all the amount. I plan not to change the main query as it will be use by other function. So i need to come up with new query to get the total of all 3 queries..
Advertisement
Answer
Your 3 queries is stranger, could it run successfully while using GROUP BY
without any aggregate function?
Your expected query should has structure like this:
SELECT col1, SUM(col2) AS TAmount FROM ( query_1 --SELECT col1, col2, ... UNION ALL query_2 --SELECT same number of columns as query_1. -- Name of columns could be any name, but using UNION ALL, in output the DBMS will only use names of columns in query_1 UNION ALL query_3 --SELECT same number of columns as query_1. ) GROUP BY col1 ORDER BY col1;
So for your edited question, the final query would be:
SELECT PaymentDate, SUM(amount) AS TAmount FROM ( SELECT CONVERT(CHAR(10), DatePayment, 120) AS PaymentDate, IsNull(SUM(UnitPrice * Quantity), 0) AS amount FROM POSSales P INNER JOIN POSSalesDetails PD ON P.ID = PD.SalesID INNER JOIN Payments PAY ON P.PaymentID = PAY.ID INNER JOIN POSItems PDI ON PD.ItemID = PDI.ID WHERE DatePayment >= '2019-04-01 08:00' AND DatePayment <= '2019-04-30 08:00' GROUP BY CONVERT(CHAR(10), DatePayment, 120) UNION ALL SELECT CONVERT(CHAR(10), DatePayment, 120) AS PaymentDate, IsNull(SUM(TotalAmount), 0) --AS amount FROM ShowerBookings S INNER JOIN ShowerPayments SP ON S.ID = SP.BookingID INNER JOIN Payments PAY ON SP.PaymentID = PAY.ID WHERE DatePayment >= '2019-04-01 08:00' AND DatePayment <= '2019-04-30 08:00' GROUP BY CONVERT(CHAR(10), DatePayment, 120) UNION ALL SELECT CONVERT(CHAR(10), DatePayment, 120) AS PaymentDate, IsNull(SUM(TotalAmount), 0) --AS amount FROM LockerBookings L INNER JOIN LockerPayments LP ON L.ID = LP.BookingID INNER JOIN Payments PAY ON LP.PaymentID = PAY.ID WHERE DatePayment >= '2019-04-01 08:00' AND DatePayment <= '2019-04-30 08:00' GROUP BY CONVERT(CHAR(10), DatePayment, 120) ) GROUP BY PaymentDate ORDER BY PaymentDate;