极小Sales_Order_Date

Question

我有一张桌子销售发票

​

​

我需要取最小值的Sales_Order_Date并对指标Order_Amount_DC/ Order_Amount_LC/ Order_Amount_Euro_Budget进行和。

结果我会得到

​

​

我试着

select
  Sales_Order_Number_Id,
  Name,
  Sales_Order_Date,
  Customer,
  Local_Currency,
  sum(Order_Amount_DC),
  sum(Order_Amount_LC),
  sum(Order_Amount_Euro_Budget)
from
  salesinvoices
group by
  Sales_Order_Number_Id,
  Name,
  Sales_Order_Date,
  Customer,
  Local_Currency

我怎样才能使用min (Sales_Order_Date)？

Answer 1

试试下面的代码-

select
Sales_Order_Number_Id,
Name,
MIN(Sales_Order_Date), -- Add MIN to the column 
Customer,
Local_Currency,
sum(Order_Amount_DC),
sum(Order_Amount_LC),
sum(Order_Amount_Euro_Budget)
from
salesinvoices
group by
Sales_Order_Number_Id,
Name,
--Sales_Order_Date, -- Remove this column from GROUP BY
Customer,
Local_Currency

Answer 2

尝尝这个。

select
  min(t1.Sales_Order_Date),
  t1.Sales_Order_Number_Id,
  t1.Name,
  t1.Customer,
  t1.Local_Currency,
  t1.s1,
  t1.s2,
  t1.s3
from
  (
    select
      Sales_Order_Number_Id,
      Name,
      Sales_Order_Date,
      Customer,
      Local_Currency,
      sum(Order_Amount_DC) as s1,
      sum(Order_Amount_LC) as s2,
      sum(Order_Amount_Euro_Budget) as s3
    from
      salesinvoices
    group by
      Sales_Order_Number_Id,
      Name,
      Sales_Order_Date,
      Customer,
      Local_Currency
  ) t1
group by
  t1.Sales_Order_Number_Id,
  t1.Name,
  t1.Customer,
  t1.Local_Currency,
  t1.s1,
  t1.s2,
  t1.s3