Hey guys, i have an sql statement that joins four tables, i'm using this data to create a dashboard in power BI. Each table has the same columns but the tables themselves get data from different sources within a hospital. For example my billing_ippharmacy table gets its data from the In Patient (ip) ,my billing_paynowpharmacy gets its data from the cash department, billing_paylaterpharmacy gets its data from people who are paying via insuarance i need to split these tables to generate the report based on where the data is coming from. Currently trying to use an alias but i cant seem to slice by alias any help would be much appreciated
SELECT SUM(CountMedicine),medicinecode, medicinename, quantity, SUM(Revnue), billdate,billtype, accountname, paymentstatus, locationname, locationcode, categoryname
FROM(
SELECT * FROM(
SELECT COUNT(b.medicinecode) AS CountMedicine,medicinecode, b.medicinename, b.quantity,SUM(b.amount) AS Revnue ,b.billdate,b.billtype, b.accountname, b.paymentstatus, b.locationname, b.locationcode, a.categoryname
FROM billing_paynowpharmacy as b
LEFT JOIN master_itempharmacy as a ON (b.medicinecode = a.itemcode)
WHERE billdate BETWEEN "2022-01-01" AND "2022-01-31"
GROUP BY b.billdate, b.medicinecode, a.categoryname, b.locationcode
) AS paynowpharmacy
UNION ALL
SELECT * FROM(
SELECT COUNT(c.medicinecode) AS CountMedicine,medicinecode, c.medicinename, c.quantity,SUM(c.amount) AS Revnue ,c.billdate,c.billtype, c.accountname, c.paymentstatus, c.locationname, c.locationcode, a.categoryname
FROM billing_paylaterpharmacy as c
LEFT JOIN master_itempharmacy as a ON (c.medicinecode = a.itemcode)
WHERE billdate BETWEEN "2022-01-01" AND "2022-01-31"
GROUP BY c.billdate, c.medicinecode, a.categoryname, c.locationcode
) AS paylaterpharmacy
UNION ALL
SELECT * FROM(
SELECT COUNT(d.medicinecode) AS CountMedicine,medicinecode, d.medicinename, d.quantity,SUM(d.amount) AS Revnue ,d.billdate,d.billtype, d.accountname, d.paymentstatus, d.locationname, d.locationcode, a.categoryname
FROM billing_ippharmacy as d
LEFT JOIN master_itempharmacy as a ON (d.medicinecode = a.itemcode)
WHERE billdate BETWEEN "2022-01-01" AND "2022-01-31"
GROUP BY d.billdate, d.medicinecode, a.categoryname, d.locationcode
) AS ippharmacy
UNION ALL
SELECT * FROM(
SELECT COUNT(e.medicinecode) AS CountMedicine,medicinecode, e.medicinename, e.quantity,SUM(e.amount) AS Revnue ,e.billdate,e.billtype, e.accountname, e.paymentstatus, e.locationname, e.locationcode, a.categoryname
FROM billing_externalpharmacy as e
LEFT JOIN master_itempharmacy as a ON (e.medicinecode = a.itemcode)
WHERE billdate BETWEEN "2022-01-01" AND "2022-01-31"
GROUP BY e.billdate, e.medicinecode, a.categoryname, e.locationcode
) AS externalpharmacy
)as pharmacytables
WHERE billdate BETWEEN "2022-01-01" AND "2022-01-31"
GROUP BY billdate,medicinecode, categoryname,locationcode
