What's wrong with this sql statement?

tangjinyi

SELECT dbo.Orders.Id, dbo.Orders.OrderDate, dbo.Orders.Journal, dbo.Orders.Billing,
      Billing.Name AS BillingName, Billing.Email AS BillingEmail,
      Billing.Address + ',' + Billing.Region AS BillingAddress, Billing.Zip AS BillingZip,
      Billing.Phone AS BillingPhone, dbo.Orders.Shipping,
      Shipping.Name AS ShippingName, Shipping.Email AS ShippingEmail,
      Shipping.Address + ',' + Shipping.Region AS ShippingAddress,
      Shipping.Zip AS ShippingZip, Shipping.Phone AS ShippingPhone,
      dbo.Orders.Payment, dbo.Payment.Holder, dbo.Payment.Credit, dbo.Payment.Expire,
      COUNT (dbo.Details.ProductId) AS Quantity, SUM (dbo.Product.Price) AS Total,
      dbo.Orders.Status
FROM dbo.Orders INNER JOIN
      dbo.Details ON dbo.Orders.Id = dbo.Details.OrderId INNER JOIN
      dbo.Contact AS Shipping ON dbo.Orders.Shipping = Shipping.Id INNER JOIN
      dbo.Contact AS Billing ON dbo.Orders.Billing = Billing.Id INNER JOIN
      dbo.Product ON dbo.Details.ProductId = dbo.Product.Id INNER JOIN
      dbo.Payment ON dbo.Orders.Payment = dbo.Payment.Id
GROUP BY dbo.Orders.Id, dbo.Orders.OrderDate, dbo.Orders.Journal, dbo.Orders.Billing,
      Billing.Name, Billing.Email, Billing.Address, Billing.Region, Billing.Zip, Billing.Phone,
      dbo.Orders.Shipping, Shipping.Name, Shipping.Email, Shipping.Address,
      Shipping.Region, Shipping.Zip, Shipping.Phone, dbo.Orders.Payment,
      dbo.Payment.Holder, dbo.Payment.Credit, dbo.Payment.Expire, dbo.Orders.Status


One product order.

Orders table Orders table, which contains Billing, Shiping, Payment fields, records payment contact information, overseas shipping address information. Another Details table records detailed order information, including the corresponding OrderId and product ID ProductId, so that you can use COUNT (dbo.Details.ProductId) AS Quantity, SUM (dbo.Product.Price) AS Total, to separately count the orders The total number and total amount of products included. The question is why the total amount cannot be calculated?

我是杀人狂

So many table joins, looked dazzled.

朗基奴斯

Eyes are gone

tangjinyi

Query from four tables, the order table is Orders, and the auxiliary table Details records all the selected products, records the ProductId, and already corresponds to the OrderId of the order. Can't query the total price based on the ProductId?

wgfxman

dbo.Contact AS Shipping ON dbo.Orders.Shipping = Shipping.Id INNER JOIN
dbo.Contact AS Billing ON dbo.Orders.Billing = Billing.Id INNER JOIN

This is rarely used. . Common is the following
INNER JOIN dbo.Contact ON Orders.Shipping = Contact.Id AND Orders.Billing = Contact.Id

Also, see if the NULL value turns into 0 or ...

tangjinyi

Isn't this the main reason?
The key is COUNT (dbo.Details.ProductId) AS Quantity, SUM (dbo.Product.Price) AS Total,
This problem, I can remove SUM (dbo.Product.Price) AS Total, but there is no total column.

wgfxman

Can't count the total amount?

Is the result a wrong number or an error?
. . .

gaoqingfeng

The landlord is too strong, the first time I saw group by followed by so many fields, I have learned!