Counting Values based on distinct values from another Column

Question

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

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

Example:

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

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

Is this possible using SQL Server 2014.

Answer 1

You can add DISTINCT to a COUNT:

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

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

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

Edit:

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;

or

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

or

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