CODEWITHSUNDEEP
sql
user2356581
user2356581

Reputation: 1

Comparing 2 values in the Same column

I have a table like following : 

  Orderserialno SKU  Units
  1234-6789     2x3   5
  1234-6789     4x5   7
  1334-8905     4x5   2
  1334-8905     6x10  2

I need to get the count of distinct orderserialno where Units are not equal within a orderserialno. There could be more combinations of Sku's in an order than what I have mentioned but the eventual goal is to get those orders where units corresponding to various SKUs (in that order) are not equal.

In the above case I should get answer as 1 as orderserialno 1234-6789 has different units.

Thanks

Upvotes: 0

Views: 93

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

To get the list of such order numbers, use an aggregation query:

select OrderSerialNo
from t
group by OrderSerialNo
having min(Units) <> max(Units)

This uses a trick to see if the units value changes. You can use count(distinct), but that usually incurs a performance overhead. Instead, just compare the minimum and maximum values. If they are different, then the value is not constant.

To get the count, use this as a subquery:

select count(*)
from (select OrderSerialNo
      from t
      group by OrderSerialNo
      having min(Units) <> max(Units)
     ) t

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

This is a relatively simple GROUP BY query:

SELECT Orderserialno, Units
FROM MyTable
GROUP BY Orderserialno, Units
HAVING COUNT(1) > 1

This would give you all pairs (Orderserialno, Units). To project out the Units, nest this query inside a DISTINCT, like this:

SELECT DICTINCT(Orderserialno) FROM (
    SELECT Orderserialno, Units
    FROM MyTable
    GROUP BY Orderserialno, Units
    HAVING COUNT(1) > 1
)

If you need only the total count of Orderserialnos with multiple units, replace DICTINCT(Orderserialno) with COUNT(DICTINCT Orderserialno).

Upvotes: 2

Related Questions