根据来自另一列的不同值计算值 [英] Counting Values based on distinct values from another Column

问题描述

我需要根据 orderlineNo 不同来计算有多少个 orderline.每个订单号都不同，但每个订单的订单号是相同的.即订单上有 9 行，那么订单行号将从 1 - 9 开始.如果在另一个订单上有 3 个订单行，它们将从 1 - 3 开始

I need to calculate how many orderlines there are based on the orderlineNo being distinct. Each OrderNo is different BUT the OrderLineNo is the same for each order. i.e. 9 lines on a order then order lines number will go from 1 - 9. The same if on another order there are 3 orderlines they will go from 1 - 3

但是在 orderlineno 中可能会有相同的订单号 - 为此我只想计算一次

But in orderlineno there could be orderline numbers that are the same - for this I only want to count it once

示例:

OrderNo        OrderLineNo
987654             1
987654             2
987654             2
987654             3
987654             4
987654             5
987654             6
987654             7

这里的订单行总数为 7.有两个订单行，其中 2 行，我希望它们只计算一次.

The total order lines here is 7. There are two order lines with 2 and I want them to only be counted once.

这是否可以使用 SQL Server 2014.

Is this possible using SQL Server 2014.

推荐答案

您可以将 DISTINCT 添加到 COUNT:

You can add DISTINCT to a COUNT:

select OrderNo, count(distinct OrderLineNo)
from tab
group by OrderNo;

或者如果 OrderLineNo 总是从 1 开始并且没有间隙地增加:

Or if OrderLineNo always starts with 1 and increases without gaps:

select OrderNo, max(OrderLineNo)
from tab
group by OrderNo;

根据评论，它不是每个 OrderNo 的计数，而是全局计数.您需要使用派生表:

Based on the comment it's not a count per OrderNo, but a global count. You need to use a Derived Table:

select count(*)
from
 (select distinct OrderNo, OrderLineNo
  from tab
 ) as dt;

或

select sum(n)
from
 (select OrderNo, max(OrderLineNo) as n
  from tab
  group by OrderNo
 ) as dt;

或

select sum(Dist_count)
from
 ( select OrderNo,count(distinct OrderLineNo) as Dist_count
   from Table1
   group by OrderNo
 ) as dt

这篇关于根据来自另一列的不同值计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！