数量和最小日期的组项目

发布于 2025-01-30 04:29:53 字数 882 浏览 3 评论 0原文

我有一个这样的表：

我的目的是创建一个表，该表显示每个订单NUM和ITEM：

总量：订购
最低预期交货日期
总数

的：

我尝试使用

SELECT 'Order num.',
       Item,
       SUM(table.Qty) AS 'Total qty',
       Supplier,
       SUM(table.Qty) * UnitValue AS 'Total price',
       MIN(ExpectedDeliveryDate),
       MIN(ConfirmedDeliveryDate)
  FROM table
 GROUP BY 'Order num.', Item, Supplier, 
           MIN(ExpectedDeliveryDate), MIN(ConfirmedDeliveryDate)
 ORDER BY 'Order num.', Item

，但它不起作用。你能帮助我吗？非常感谢。

原文

I've got a table like this:

My intent is to create a table which shows, for each Order num and Item:

The Total quantity ordered
The minimum Expected delivery date
The minimum Confirmed Delivery Date

Like this:

I tried to use

SELECT 'Order num.',
       Item,
       SUM(table.Qty) AS 'Total qty',
       Supplier,
       SUM(table.Qty) * UnitValue AS 'Total price',
       MIN(ExpectedDeliveryDate),
       MIN(ConfirmedDeliveryDate)
  FROM table
 GROUP BY 'Order num.', Item, Supplier, 
           MIN(ExpectedDeliveryDate), MIN(ConfirmedDeliveryDate)
 ORDER BY 'Order num.', Item

But it doesn't work. Can you help me? Many thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

羁〃客ぐ 2025-02-06 04:29:53

一个问题：您是否真的有一个订单num在其中空间？我认为这是不可能的，至少我从未为数据库列名称使用过这样的名称。
我进行了一个查询，假设该列被命名为“ ordernum”。同样，对于订购日期。

通常，在“选择”子句中，您必须使用汇总函数（min，max，...）对于所有不属于“组”的列的列。这意味着除ordernum和项目

我不确定所需的“总价格”列，但这意味着所有列，但这应该或多或少产生结果：

SELECT Ordernum AS 'Order num.',
       MAX(Orderdate) AS 'Order date.',
       Item,
       SUM(Qty) AS 'Total qty',
       MAX(UnitValue) AS 'Unit price', -- "MAX" only need for the syntax here, no real aggregate
       Max(Supplier),                  -- "MAX" only need for the syntax here, no real aggregate
       MIN(ExpectedDeliveryDate),
       MIN(ConfirmedDeliveryDate)
  FROM table
 GROUP BY Ordernum, Item 
 ORDER BY Ordernum, Item

A question: Do you really have a column Order num with as space in it? I do not think this is possible, at least I never used such a name for a database column name.
I made a query that assumes the column is named "Ordernum". Likewise for the Order date.

In general, in the SELECT clause you have to use aggregate functions (MIN, MAX, ...) for all those columns that are not part of the "GROUP BY". That means for all columns except Ordernum and Item

I am not sure about the desired "Total price" column, but this should more or less produce the result:

SELECT Ordernum AS 'Order num.',
       MAX(Orderdate) AS 'Order date.',
       Item,
       SUM(Qty) AS 'Total qty',
       MAX(UnitValue) AS 'Unit price', -- "MAX" only need for the syntax here, no real aggregate
       Max(Supplier),                  -- "MAX" only need for the syntax here, no real aggregate
       MIN(ExpectedDeliveryDate),
       MIN(ConfirmedDeliveryDate)
  FROM table
 GROUP BY Ordernum, Item 
 ORDER BY Ordernum, Item

回复收藏 0 原文

~没有更多了~