在PostgreSQL中获取Total项目的SUM

Question

表格如下:

Orders
-----------
user_id
state

Items
-----------
order_id
product_id
total
Products
----------
user_id

Users
---------
name
email

关系如下:

User
has_many :products
Order
has_many :items
belongs_to :users
Product
has_many :items
belongs_to :user
Item
belongs_to :items

我想要得到项目的总和。拥有产品的每个用户的总数

我现在有以下SQL语句:

SELECT *, SUM(items.total) as totalprice 
FROM "orders" 
INNER JOIN "items" ON "items"."order_id" = "orders"."id"
INNER JOIN "products" ON "products"."id" = "items"."product_id"
INNER JOIN "users" ON "users"."id" = "products"."user_id"
WHERE "orders"."state" = 'complete'
GROUP BY users.id

或

Order.complete.joins(:items => {:product => :user}).select("*, SUM(items.total) as totalprice").group('users.id')

以上内容不起作用。我觉得这应该很容易。

Answer 1

在group by查询中，您需要包含group by子句中未聚合的所有列。如果您将查询更改为此，您可能会有更好的运气

SELECT users.id, SUM(items.total) as totalprice 
FROM "orders" 
INNER JOIN "items" ON "items"."order_id" = "orders"."id"
INNER JOIN "products" ON "products"."id" = "items"."product_id"
INNER JOIN "users" ON "users"."id" = "products"."user_id"
WHERE "orders"."state" = 'complete'
GROUP BY users.id

如果希望输出更多列，请将它们添加到选择行并按行添加到