Query problem

[habneh]

Here ia another query problem, I know for most of you guys it is easy.

I have a table say
tbl

id Catagory values

1 Min 0
1 Max 8
2 MIN 18
2 Max NULL
3 MIN NULL
3 Max 60

NB if min or max value is not given for min we take 0 for max we take 1000

each id has min and max catagory of values. I want to count those id which are
<1 not inclusive
between 1 and 18 inclusive
greater than 18 not inclusive

eg for id 1 it has min 0 and max 8, it should be counted under <1 and between 1 and 8

therefore the resultset should look like

catagory count
<1 2
between 1 and 18 3
>18 2


thanks

 

[monksnake]

Are you a student? You always have query "exercises" that you ask.

<.

 

[habneh]

No I am not

I am a tech lead for a team, my job involves many TSQL queries, which some of them are difficult for me to write like this one.
Thank you for asking me

 

[monksnake]

Oh ok, just curious.

<.

 

[Lamprey13]

I'm not sure I'm following how you get those counts, but this might get you going:

-- Setup
DECLARE @T TABLE(ID INT, Category VARCHAR(3), Value INT)

INSERT @T
SELECT 1, 'Min', 0
UNION SELECT 1, 'Max', 8
UNION SELECT 2, 'MIN', 18
UNION SELECT 2, 'Max', NULL
UNION SELECT 3, 'MIN', NULL
UNION SELECT 3, 'Max', 60

-- Query
SELECT	
	CASE 
		WHEN (Value IS NULL AND Category = 'MIN') OR Value < 1 THEN '<1'
		WHEN Value >= 1 AND Value <= 18 THEN 'between 1 and 18'
		WHEN (Value IS NULL AND Category = 'Max') OR Value > 18 THEN '>18'
		ELSE 'Unknown'
	END AS Category,
	SUM
	(
	CASE 
		WHEN (Value IS NULL AND Category = 'MIN') OR Value < 1 THEN 1
		WHEN Value >= 1 AND Value <= 18 THEN 1
		WHEN (Value IS NULL AND Category = 'Max') OR Value > 18 THEN 1
		ELSE 0
	END
	) AS [Count]
FROM 
	@T
GROUP BY
	CASE 
		WHEN (Value IS NULL AND Category = 'MIN') OR Value < 1 THEN '<1'
		WHEN Value >= 1 AND Value <= 18 THEN 'between 1 and 18'
		WHEN (Value IS NULL AND Category = 'Max') OR Value > 18 THEN '>18'
		ELSE 'Unknown'
	END

-Ryan

 

[habneh]

ye I am a C# guy, this problem it might take me one day to solve it but for you guys may be a fraction of second.
that is why I came here seeking help