So I've been working with SQL Server 2014 Express for the first time and I've encountered a problem.
Basically, what I've been asked to do (as part of an assignment in school) is to return values from a regular "customer-table", based entirely on the customer's stored location.
My query looks like this atm:
SELECT
City,
COUNT(Clients.ClientNo) AS AmountOfClients,
Orders.Pieces * products.Price TotalPrice
FROM
Clients
INNER JOIN
ClientOrder ON Clients.ClientNo = ClientOrder.ClientNo
INNER JOIN
Orders ON ClientOrder.OrderNo = Orders.OrderNo
INNER JOIN
products ON Orders.ProductNo = products.ProductNo
GROUP BY
City, ClientOrders.Pieces, products.Price
HAVING
COUNT(Clients.ClientNo) > 1
Now to explain it - I've been trying to find all customers where the contents of the "City" column re-occurs and return only those - along with the total amount of their orders (seen here as currency) based on the contents of three other tables (which is why the JOINs are there). The latter part is working, but the wrong clients get returned and instead of returning the actual "ClientNo", only the amount of clients found get returned. I can't seem to find the right course of action here.
Ideally, the returned information/output would look somewhat like this:
City, ClientNo, TotalCost -- for each client that lives in the same City as another
Any input would be appreciated.
You should use only City in the group by clause and also use
SUM(Orders.Pieces * products.Price) TotalPrice
in the SELECT clause to get the total sum from each city order (Sum of Products).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments