By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

HAVING prob !

P: n/a
Bonjour,
CREATE TABLE [dbo].[MAND](
[Mat] [varchar](5)
[Dur] [varchar](1)
) ON [PRIMARY]

Mat Dur
16030 d
16030 i
31217 i
10000 d
12000 i
10000 d
31217 d
35000 d
36000 i
35000 d

Je voudrais avoir le resulat suivant (i need this result) :

10000 d
35000 d

Car ils ont tous les deux "d". J'ai beau faire un regroupement (group
by) par Mat avec un having ne marche pas.

Comment faire ?
Merci d'avance

Jun 2 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Is this what you want?
SELECT Mat,
Dur
FROM MAND
GROUP BY Mat,Dur
HAVING COUNT(*)=2

Jun 2 '06 #2

P: n/a
Try:

select
Mat
, Dur
from
MAND
group by
Mat
, Dur
having
count (*) > 1

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<da*****@yahoo.com> wrote in message
news:11********************@c74g2000cwc.googlegrou ps.com...
Bonjour,
CREATE TABLE [dbo].[MAND](
[Mat] [varchar](5)
[Dur] [varchar](1)
) ON [PRIMARY]

Mat Dur
16030 d
16030 i
31217 i
10000 d
12000 i
10000 d
31217 d
35000 d
36000 i
35000 d

Je voudrais avoir le resulat suivant (i need this result) :

10000 d
35000 d

Car ils ont tous les deux "d". J'ai beau faire un regroupement (group
by) par Mat avec un having ne marche pas.

Comment faire ?
Merci d'avance

Jun 2 '06 #3

P: n/a

markc...@hotmail.com schreef:
Is this what you want?
SELECT Mat,
Dur
FROM MAND
GROUP BY Mat,Dur
HAVING COUNT(*)=2


No.

When i group by 'Mat' i need to filter by 'Mat' having only 'd' in
'Dur'. I d'ont want Mat with 'i' in Dur.

Jun 2 '06 #4

P: n/a
Maybe this

SELECT Mat,
Dur
FROM MAND
WHERE Dur='d'
GROUP BY Mat,Dur
HAVING COUNT(*)=2

Jun 2 '06 #5

P: n/a
other example to help you :

Mat Dur
16030 d
16030 i
31217 i
10000 d
12000 i
10000 d
31217 d
35000 d
36000 i
35000 d
35000 i
i need this result :

10000 d

Now, 35000 have one 'i' in 'Dur', so i don't need it.
da*****@yahoo.com schreef:
markc...@hotmail.com schreef:
Is this what you want?
SELECT Mat,
Dur
FROM MAND
GROUP BY Mat,Dur
HAVING COUNT(*)=2


No.

When i group by 'Mat' i need to filter by 'Mat' having only 'd' in
'Dur'. I d'ont want Mat with 'i' in Dur.


Jun 2 '06 #6

P: n/a

SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1

Jun 2 '06 #7

P: n/a

ma******@hotmail.com schreef:
SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1


Sorry, it's not correct :

if i insert

36000 i
36000 i

and apply your SQL, i have some result like :

10000 d
16030 d
31217 d
36000 d (????)

perhaps, i d'ont expline vers well !

Jun 2 '06 #8

P: n/a

da*****@yahoo.com schreef:
ma******@hotmail.com schreef:
SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1


Sorry, it's not correct :

if i insert

36000 i
36000 i

and apply your SQL, i have some result like :

10000 d
16030 d
31217 d
36000 d (????)

perhaps, i d'ont expline vers well !


I will try to expline more :
this is the table with groups:

Mat Dur
16030 d
16030 i

31217 i
31217 d
31217 d
31217 d

10000 d
10000 d
10000 d

35000 d

I'am interested only by the 2 groups : 1000 and 35000 because the
haven't any 'i' in the colonn Dur.

Jun 2 '06 #9

P: n/a

da*****@yahoo.com schreef:
da*****@yahoo.com schreef:
ma******@hotmail.com schreef:
SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1


Sorry, it's not correct :

if i insert

36000 i
36000 i

and apply your SQL, i have some result like :

10000 d
16030 d
31217 d
36000 d (????)

perhaps, i d'ont expline vers well !


I will try to expline more :
this is the table with groups:

Mat Dur
16030 d
16030 i

31217 i
31217 d
31217 d
31217 d

10000 d
10000 d
10000 d

35000 d

I'am interested only by the 2 groups : 10000 and 35000 because the
haven't any 'i' in the colonn Dur.


Jun 2 '06 #10

P: n/a


SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
AND MAX(Dur)='d'

Jun 2 '06 #11

P: n/a

ma******@hotmail.com schreef:
SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
AND MAX(Dur)='d'


sorry, it's not correct

Example:
16030 d
16030 d
31217 d
10000 d
12000 i
10000 d
31217 d
35000 d
36000 i
35000 d
35000 d
36000 i
45 d
45 d
45 i
250 d
250 i
10 i
10 i

result with your query :
10000 d
16030 d
31217 d

reult that i need:
10000 d
16030 d
31217 d
35000 d

why COUNT(*)=2 ?

Jun 2 '06 #12

P: n/a
The problem is I'm trying to guess your business rules
SELECT Mat,
MAX(Dur) AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)>1 AND COUNT(DISTINCT Dur)=1
AND MAX(Dur)='d'

Jun 2 '06 #13

P: n/a
Try:

SELECT Mat,
Dur
FROM MAND
GROUP BY Mat, Dur
HAVING SUM (CASE WHEN Dur = 'i' THEN 1 ELSE 0 END) = 0
--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<da*****@yahoo.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...

da*****@yahoo.com schreef:
ma******@hotmail.com schreef:
SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1


Sorry, it's not correct :

if i insert

36000 i
36000 i

and apply your SQL, i have some result like :

10000 d
16030 d
31217 d
36000 d (????)

perhaps, i d'ont expline vers well !


I will try to expline more :
this is the table with groups:

Mat Dur
16030 d
16030 i

31217 i
31217 d
31217 d
31217 d

10000 d
10000 d
10000 d

35000 d

I'am interested only by the 2 groups : 1000 and 35000 because the
haven't any 'i' in the colonn Dur.

Jun 2 '06 #14

P: n/a

ma******@hotmail.com schreef:
The problem is I'm trying to guess your business rules
SELECT Mat,
MAX(Dur) AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)>1 AND COUNT(DISTINCT Dur)=1
AND MAX(Dur)='d'


mmm very interresting. Thanks

Jun 2 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.