See whether a groupped item have more than one value in a database column

Question

I am working on SQL database where there are several thousands of lines in one table. This table (product_lines) looks like this:

sku price   Discount    FinalPrice
1   29      5           24
1   29      5           24
2   30      5           25
2   30      0           30
3   15      0           15
3   15      0           15

Each line contains SKU's information when used in a receipt. SKUs may repeat within the rows (eg sku:2) but discount vary each time. I am trying to collect the items that exist both with and without a value in the column discount -- to collect the SKUs that have sold with and without discount. The result I am looking for should look like:

  sku price   Discount    FinalPrice
  2   30      5           25
  2   30      0           30

How can I do this?

Answer 1

You can either use an IN clause with an intersect

select *
from product_lines
where sku in (select sku 
              from product_lines
              where discount > 0
              intersect
              select sku
              from product_lines
              where discount = 0);

Using exists might be faster with SQL Server:

select *
from product_lines p1
where exists (select *
              from product_lines p2
              where p2.discount > 0
               and p2.sku = p1.sku)
  and exists (select * 
              from product_lines p3
              where p3.sku = p1.sku
                and p3.discount = 0);

Answer 2

You can separately count items with and without discount per SKU, then compare the two results and return the rows where the counts are greater than zero.

If you wanted just the SKUs and their prices (provided that the price is the same for the same SKU in this table), you could do all the job in one go like this:

SELECT
  sku,
  price
FROM
  dbo.YourTable
GROUP BY
  sku,
  price
HAVING
      COUNT(CASE WHEN Discount <> 0 THEN 1 END) > 0
  AND COUNT(CASE WHEN Discount =  0 THEN 1 END) > 0
;

Output for your example:

sku  price
---  -----
2    30

As you need other details as well, you can use the above as a subquery like this:

SELECT
  sku,
  price,
  Discount,
  FinalPrice
FROM
  dbo.YourTable
WHERE
  sku IN
  (
    SELECT
      sku
    FROM
      dbo.YourTable
    GROUP BY
      sku,
      price
    HAVING
          COUNT(CASE WHEN Discount <> 0 THEN 1 END) > 0
      AND COUNT(CASE WHEN Discount =  0 THEN 1 END) > 0
  )
;

An alternative, and possibly better, solution would be to use window aggregation:

SELECT
  sku,
  price,
  Discount,
  FinalPrice
FROM
  (
    SELECT
      sku,
      price,
      Discount,
      FinalPrice,
      DiscountedPerSKU    = COUNT(CASE WHEN Discount <> 0 THEN 1 END)
                            OVER (PARTITION BY sku),
      NotDiscountedPerSKU = COUNT(CASE WHEN Discount =  0 THEN 1 END)
                            OVER (PARTITION BY sku)
    FROM
      dbo.YourTable
  ) AS derived
WHERE
  DiscountedPerSKU > 0
  AND NotDiscountedPerSKU > 0
;

Both options would return the same expected output:

sku  price  Discount  FinalPrice
---  -----  --------  ----------
2    30     5         25
2    30     0         30

Of the two options, the latter is better in that it scans the table once while the former scans it twice (in the main SELECT and in the subquery).

All three queries can be tested at dbfiddle.uk.

Answer 3

So here is what I imagine your table looks like, and some sample data. As others mention, you don't provide enough info, so here is simply a stab at it given what you've provided.

EDIT: partition to see count of items of same SKU loaded

create table #test
(sku int, price float, discount float)

insert into #test values
(1, 29.99, 4.99),
(2, 30.00, 0.00),
(2, 2.99, 0.99),
(3, 15.50, null)

select row_number() over (partition by sku order by price) PartitionBySKU, 
sku, price, coalesce(discount, 0) Discount,
price - (coalesce(discount,0)) FinalPrice
from #test

Results:

PartitionBySKU  sku price   Discount    FinalPrice
1               1   29.99   4.99        25
1               2   2.99    0.99        2
2               2   30      0           30
1               3   15.5    0           15.5

Answer 4

If the data exists exactly as stated in the question, this will find the rows that have multiple entries for Discount.

DECLARE @Test TABLE
(
SKU INT,
Price FLOAT,
Discount FLOAT,
FinalPrice FLOAT
)

INSERT INTO @Test
    ( SKU, Price, Discount, FinalPrice )
VALUES  (1, 29, 5, 24),
    (1, 29, 5, 24),
    --(1, 29, 0, 29),
    (2, 30, 5, 25),
    (2, 30, 0, 30),
    (3, 15, 0, 15),
    (3, 15, 0, 15)


SELECT  SKU,
    Price,
    Discount,
    FinalPrice--,
    --COUNT(*) AS StarCount,
    --COUNT(SKU) AS SKUCount,
    --COUNT(Price) AS PriceCount,
    --COUNT(Discount) AS DiscountCount,
    --COUNT(FinalPrice) AS FinalPriceCount

FROM    @Test

GROUP BY    SKU, Price, Discount, FinalPrice
HAVING COUNT(*) = 1

If you have a duplicate row and then an additional row with a different discount (uncomment the third row inserted) then you would not get two values for SKU = 1, just the row with the differing discount value.

If you remove the comments before the COUNT(x) columns, and add a comment before the HAVING line, it may help you understand why the query works. The rows that are completely duplicated are eliminated since their COUNT(*) value = 2. This leaves the rows that only appear once.