Query to only to return rows IF all rows have data-CodePudding

How would I write a query based on the tables below so that the query would return rows if the table had data like #Test1.

If the same table contains data like #test2 where the sales for at least 1 row is 0 AND there are more than 8 ProductTypes then the query should not return anything or just return a 0

Create and Insert Script:

CREATE TABLE #TEST1
(
    ProductType nvarchar(30),
    Sales int,
    Tax int,
    Discount int

)


INSERT INTO #TEST1 (ProductType, Sales, Tax, Discount)
VALUES
('BIKES',100,1, 2),
('TRUCKS',100,1, 2),
('CARS',100,1, 2),
('SCOOTERS',100,1, 2),
('BOATS',100,1, 2),
('PLANES',100,1, 2),
('TANKS',100,1, 2),
('HELICOPTERS',100,1, 2),
('FOOD',100,1, 2),
('DRINKS',100,1, 2)

CREATE TABLE #TEST2
(
    ProductType nvarchar(30),
    Sales int,
    Tax int,
    Discount int

)

INSERT INTO #TEST2 (ProductType, Sales, Tax, Discount)
VALUES
('BIKES',100,1, 2),
('TRUCKS',100,1, 2),
('CARS',100,1, 2),
('SCOOTERS',100,1, 2),
('BOATS',100,1, 2),
('PLANES',100,1, 2),
('TANKS',100,1, 2),
('HELICOPTERS',0,0,0),
('FOOD',0,0,0),
('DRINKS',0,0,0)


select * from #TEST1
select * from #TEST2

CodePudding user response：

Can you see if the following not exists criteria is what you are expecting

select * 
from #TEST1 t
where not exists (
  select * from #test1 t2
  having Count(distinct ProductType)> 8 
  and sum(case when sales = 0 then 1 end) > 0
);

CodePudding user response：

This is based solely on the following comment:

, I only want to return all the rows IF there are > 8 rows and none of the rows have zero sales.

If this is what you are after, you can use an EXISTS against #TEST2 and COUNT both the number of rows and how many have a value of 0 in the HAVING, and check that the values are > 8 and 0 respectively:

SELECT *
FROM #TEST1 T1
WHERE EXISTS (SELECT 1
              FROM #TEST2 T2
              HAVING COUNT(*) > 8
                AND COUNT(CASE T2.Sales WHEN 0 THEN 1 END) = 0);

CodePudding user response：

Assuming Sales are never negative, you can find whether any Sales value is 0 with MIN(Sales).

You may be able to do this using a CTE to calculate the Minimum sales and row count over the entire result set

;with x as (
    select *, MIN(Sales) OVER() as MinSales,COUNT(*) OVER() as Cnt
    from #TEST2
)
select * from x
where MinSales!=0 OR Cnt<=8

This may be able to calculate the result with a single table scan.

You can also use a subquery and HAVING but that will result in two table scans.

select * 
from #TEST2
where exists (select 1 
              from #TEST2 
              HAVING MIN(Sales)!=0 OR COUNT(*)<=8)

The execution plans for the two cases show that the first is half as expensive as the second. That's because the second query has to scan the entire table twice, once to calculate the counts and once to return the rows.