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.

Pairoar

Hi Thanks for your reply, Although this works, it shows the total per order,I just need the complete total. – Pairoar

Instead of commenting your question you should edit it and add the desired result and a complete sample. You don't need to show hundreds, but it would be helpful to see one which contains more than one OrderNo. Also, if you are referring to my answer below then comment that instead. – Baklava

possible duplicate of SQL Server query - Selecting COUNT(*) with DISTINCT – Imagination

Based on the logic of your table, why not just select the MAX OrderLineNo? – Wojcik

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

Repossess

Answer 2

I guess you want this:

SELECT OrderNo, COUNT(distinct OrderLineNo) as CntDistOrderLineNoPerOrderNo
FROM Table1
GROUP BY OrderNo

demo

So for every OrderNo the count of dictinct OrderLineNo which is 7 for 987654.

If you instead want the sum of all distinct OrderLineNo as commented.

WITH CTE AS
(
  SELECT OrderNo,
         MAX(OrderLineNo) as MaxOrderLineNoPerOrderNo
  FROM Table1
  GROUP BY OrderNo
)
SELECT SUM(MaxOrderLineNoPerOrderNo) AS SumOrderLineNoPerOrderNo
FROM CTE

Demo

Baklava

Sorry I meant to comment on this one. Yes this works But it shows all Order No;s with the respective total. How can I get it to show The complete total of all of them. – Pairoar

So you want the sum of all distinct OrderLineNo? – Baklava

Yes, a sum of all distinct orderlines (but distinct on orderno level) – Pairoar

I edited your SQLFiddle sqlfiddle.com/#!6/2fb5a/1 If you run it will give you 7 & 6 (total = 13) if you remove "Orderno," and group by. it will only give you 7. i need the 13 :) – Pairoar

@Crampo: have a look. – Baklava

Answer 3

Use Distinct in count aggregate

select count(distinct OrderLineNo) as Dist_count
from yourtable

Spinel

Answer 4

A solution without distinct, but it requires applying group by twice:

select orderNo , count(*) from
  (select orderNo from tbl group by orderNo,orderlineNo) t1 group by orderNo

Ogg

Answer 5

Check my answer and modified it as your need:

SELECT DEVICE,
COUNT(CASE WHEN ANOMALY_SEVERITY = 'Critical' THEN 'Critical' END) as 'Critical', 
COUNT(CASE WHEN ANOMALY_SEVERITY = 'High' THEN 'High' END) as 'High',
COUNT(CASE WHEN ANOMALY_SEVERITY = 'Medium' THEN 'Medium' END) as 'Medium', 
COUNT(CASE WHEN ANOMALY_SEVERITY = 'Low' THEN 'Low' END) as 'Low',
COUNT(CASE WHEN ANOMALY = 'True' THEN 'Total' END) as 'Total'
FROM <table_name> WHERE ANOMALY='True' GROUP BY DEVICE

Trichloroethylene