I have two tables; hotelservices and guestorder
s_id(pk) serviceType serviceName
g_id(pk) serviceType totalAmount balanceDue PaidAmount orderDate
I want to get the sum of totalAmount,balanceDue,PaidAmount based upon the serviceType from the hotelservice table.
That is, it will iterate and group the hotelservices.serviceType and get the associated data from the guestorder table based upon the serviceType column.
My attempt so far:
g.OrderDate as saleDate,
sum(g.TotalAmount) as TotalSales,
sum(g.BalanceDue) as TotalBalanceDue,
sum(g.PaidAmount) as TotalPaid,
h.ServicesType as services
FROM guestorder as g join hotelservices as h on g.ServiceType=h.ServicesType
group by saleDate
but it is not what i want. i need it to show others serviceType even if it has nothing in the guestorder.i don't know if i am making sense at all. thanks in advance
Maybe reverse your join, turn it into a LEFT OUTER JOIN and fix the problem with your GROUP BY, e.g.:
SELECT g.OrderDate as saleDate, g.ServiceType, sum(g.TotalAmount) as TotalSales, sum(g.BalanceDue) as TotalBalanceDue, sum(g.PaidAmount) as TotalPaid, h.ServicesType as services FROM hotelservices as h LEFT JOIN guestorder as g ON g.ServiceType=h.ServicesType GROUP BY g.OrderDate, g.ServiceType, h.ServicesType;
i need it to show others serviceType even if it has nothing in the guestorder
from this im assuming you have data that doesn't match... when you use a JOIN or INNER JOIN (because thats what JOIN defaults to) you are filtering out anything that doesn't match. if you want to join without filtering then do a LEFT JOIN..
you should add COALESCE() so that you don't have NULL values like so
SELECT COALESCE(g.OrderDate, '') saleDate, COALESCE(g.ServiceType, '') guestServiceType, COALESCE(sum(g.TotalAmount), 0) TotalSales, COALESCE(sum(g.BalanceDue), 0) TotalBalanceDue, COALESCE(sum(g.PaidAmount), 0) TotalPaid, h.ServicesType as services FROM hotelservices h LEFT JOIN guestorder g ON g.ServiceType = h.ServicesType GROUP BY h.serviceType, saleDate
your select needs to be FROM the hotel services table so that way you have all of the service types.. then LEFT JOIN the guestorder table so that way there is no filtering.