问题描述:

I have two tables; hotelservices and guestorder

hotelservices

s_id(pk) serviceType serviceName

guestOrder table:

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:

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

NOTE:

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.

相关阅读:
Top