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.

Hi Thanks for your reply, Although this works, it shows the total per order,I just need the complete total. — Crampo, Jul 17 '15 at 14:40
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. — Tim Schmelter, Jul 17 '15 at 14:42
possible duplicate of [SQL Server query - Selecting COUNT(\*) with DISTINCT](http://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct) — Tanner, Jul 17 '15 at 14:47
Based on the logic of your table, why not just select the MAX OrderLineNo? — Tab Alleman, Jul 17 '15 at 15:32

dnoeth · Accepted Answer · 2015-07-17T15:14:00.500

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

Tim Schmelter · Answer 2 · 2015-07-17T15:08:24.977

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

edited Jul 17 '15 at 15:08

answered Jul 17 '15 at 14:32

Tim Schmelter

450,073
74
686
939

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. – Crampo Jul 17 '15 at 14:47
So you want the sum of all distinct OrderLineNo? – Tim Schmelter Jul 17 '15 at 14:49
Yes, a sum of all distinct orderlines (but distinct on orderno level) – Crampo Jul 17 '15 at 14:51
I edited your SQLFiddle http://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 :) – Crampo Jul 17 '15 at 14:55
@Crampo: have a look. – Tim Schmelter Jul 17 '15 at 15:08

score 0 · Answer 3 · answered Jul 17 '15 at 14:30

0

Use Distinct in count aggregate

select count(distinct OrderLineNo) as Dist_count
from yourtable

answered Jul 17 '15 at 14:30

Pரதீப்

91,748
19
131
172

score 0 · Answer 4 · answered Jul 17 '15 at 14:34

0

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

answered Jul 17 '15 at 14:34

Carsten Massmann

26,510
2
22
43

score 0 · Answer 5 · answered Jan 10 '23 at 09:53

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

Counting Values based on distinct values from another Column

5 Answers5

Linked