Count for a group

Transact-SQL
1

Respected All,

Pretty new to do sql, can any one please suggest how to get this.

DECLARE @T TABLE
(
SKU VARCHAR (10),
DESTINATION VARCHAR (10),
OVERS VARCHAR (10)
)

INSERT @T

SELECT '19-1405','Redmi','BAJAJ' UNION ALL
SELECT '19-1405','Redmi','KFC' UNION ALL
SELECT '9999-0','Redmi','BAJAJ' UNION ALL
SELECT '19-1405','Nokia','BAJAJ' UNION ALL
SELECT '19-1508','motorola','BAJAJ' UNION ALL
SELECT '19-1508','motorola','KFC' UNION ALL
SELECT '5555-0','motorola','BAJAJ' UNION ALL
SELECT '8888-88','sony','BAJAJ'

/*

I am trying to design two script
1st

to find out whether SKU for a particular destination group contain both over . i.e (BAJAJ and KFC)

2nd
if any of the sku in a particular destination group contain only Bajaj

*/

Thanks
pete

2

Try one of these queries, and if that is not what you are looking for, post the desired output?

SELECT
	sku , Destination
FROM
	@t
WHERE
	OVERS IN ('BAJAJ', 'KFC')
GROUP BY
	sku, Destination
HAVING
	COUNT(DISTINCT OVERS) = 2;

SELECT
	sku, Destination,
	CASE 
		WHEN COUNT(DISTINCT OVERS) = 2 THEN 'Both' 
		ELSE 'Not both' 
	END AS [Both],
	CASE 
		WHEN COUNT(DISTINCT OVERS) = 1 AND SUM(CASE WHEN OVERS = 'BAJAJ' THEN 0 ELSE 1 END ) = 0 THEN 'Only BAJAJ' 
		ELSE 'SomethingElse' 
	END AS [OnlyBAJAJ]
FROM
	@t
WHERE
	OVERS IN ('BAJAJ', 'KFC')
GROUP BY
	sku, Destination;
1 Like
3

Try these:

--1st script
SELECT SKU
FROM 
(
	SELECT SKU, OVERS, ROW_NUMBER() OVER(PARTITION BY SKU ORDER BY SKU) AS RowNumber
	FROM @T
	WHERE OVERS IN ('BAJAJ', 'KFC')
	GROUP BY SKU, OVERS
) t
WHERE RowNumber = 2

--2nd script
SELECT DISTINCT SKU
FROM @T t1
WHERE NOT EXISTS (SELECT 1 FROM @T t2 WHERE t1.SKU = t2.SKU AND t2.OVERS <> 'BAJAJ')
1 Like
4

Thank You Very Much Tara and James. is it possible to include all the column shown above in example,

5

The simplest might perhaps be to join with the result of the query that gives you the sku's (and destination if that is what you want). For example, using the first query Tara posted, you could do this:

SELECT a.*
FROM @t a
	INNER JOIN 
	(
		SELECT SKU
		FROM 
		(
			SELECT SKU, OVERS, ROW_NUMBER() OVER(PARTITION BY SKU ORDER BY SKU) AS RowNumber
			FROM @t
			WHERE OVERS IN ('BAJAJ', 'KFC')
			GROUP BY SKU, OVERS
		) t
		WHERE RowNumber = 2  
	) b ON a.sku = b.sku
1 Like
6

SELECT SKU, Destination, CASE WHEN MIN(Overs) < MAX(Overs) THEN 'Both ' + MIN(Overs) + ' and ' + MAX(Overs) ELSE 'Only ' + MIN(Overs) END AS Result FROM @t GROUP BY SKU, Destination;