Join and calculate between 3 queries

Question

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

Answer 1

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;

Answer 2

This should work doing the union per your original components. The internal I have just the date and sum grouped by date so each inner is already pre-queried before the final outer query

Select 
        CONVERT(char(10), PQ.DatePayment, 120) PaymentDate, 
        SUM(PQ.LineAmount) TotalAmount 
    from
        ( Select DatePayment, SUM(UnitPrice * Quantity) LineAmount 
            From POSSales P
                Join POSSalesDetails PD On P.ID = PD.SalesID 
                    Join POSItems PDI On PD.ItemID = PDI.ID 
                Inner Join Payments PAY On P.PaymentID = PAY.ID 
            Where DatePayment >= '2019-04-01 08:00' 
            And DatePayment <= '2019-04-30 08:00'
            group by DatePayment 
        UNION ALL
        Select DatePayment, SUM(TotalAmount) LineAmount 
            From ShowerBookings S 
                Join ShowerPayments SP On S.ID = SP.BookingID 
                    Join Payments PAY On SP.PaymentID = PAY.ID 
            Where 
                    DatePayment >= '2019-04-01 08:00' 
                And DatePayment <= '2019-04-30 08:00' 
            group by DatePayment
        UNION ALL
        Select DatePayment, SUM(TotalAmount) LineAmount 
            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 DatePayment ) PQ
    group by 
        CONVERT(char(10), PQ.DatePayment, 120) 
    order by 
        CONVERT(char(10), PQ.DatePayment, 120)

Answer 3

You can also use views. Make one view from every query, and then use the views like tables.