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
– Crampo, Commented Jul 17, 2015 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
– Tim Schmelter, Commented Jul 17, 2015 at 14:42
possible duplicate of SQL Server query - Selecting COUNT(*) with DISTINCT — Tanner
– Tanner, Commented Jul 17, 2015 at 14:47
Based on the logic of your table, why not just select the MAX OrderLineNo? — Tab Alleman
– Tab Alleman, Commented Jul 17, 2015 at 15:32

dnoeth · Accepted Answer · 2015-07-17 15:14:00Z

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 · Accepted Answer · 2015-07-17 15:08:24Z

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, 2015 at 15:08

answered Jul 17, 2015 at 14:32

Tim Schmelter

462k79 gold badges718 silver badges980 bronze badges

5 Comments

Crampo Over a year ago

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.

Tim Schmelter Over a year ago

So you want the sum of all distinct OrderLineNo?

Crampo Over a year ago

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

Crampo Over a year ago

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 :)

Tim Schmelter Over a year ago

@Crampo: have a look.

Pரதீப் · Accepted Answer · 2015-07-17 14:30:48Z

0

Use Distinct in count aggregate

select count(distinct OrderLineNo) as Dist_count
from yourtable

answered Jul 17, 2015 at 14:30

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

Carsten Massmann · Accepted Answer · 2015-07-17 14:34:16Z

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, 2015 at 14:34

Carsten Massmann

28.4k3 gold badges25 silver badges49 bronze badges

Comments

Palash Mondal · Accepted Answer · 2023-01-10 09:53:27Z

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

Collectives™ on Stack Overflow

Counting Values based on distinct values from another Column

5 Answers 5

Comments

5 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

5 Comments

Comments

Comments

Comments

Linked

Related