获得总笔数的总和PostgreSQL的
问题描述:
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
我想每个拥有该产品的用户items.total的总和
我目前有以下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
OR
Order.complete.joins(:items => {:product => :user}).select("*, SUM(items.total) as totalprice").group('users.id')
以上不起作用。我觉得这应该很容易。
答
在按查询分组中,您需要包含未在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
如果你想在输出更多的列,一行
“*不工作将它们添加到选择线和组* “不是有效的Postgres错误消息。 – 2013-04-24 09:13:32
PG ::错误:错误:当前事务中止,命令被忽略,直到事务块结束 2013-04-24 09:14:25
这不是与您的查询相关的错误消息。这是以前错误的结果。 – 2013-04-24 09:16:53