HAVING prob !

Bonjour,

CREATE TABLE [dbo].[MAND](
[Mat] [varchar](5)
[Dur] [varchar](1)
) ON [PRIMARY]

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

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'avanceIs this what you want?

SELECT Mat,
Dur
FROM MAND
GROUP BY Mat,Dur
HAVING COUNT(*)=2|||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
..
<daoud97@.yahoo.com> wrote in message
news:1149245350.517939.8790@.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|||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.|||Maybe this

SELECT Mat,
Dur
FROM MAND
WHERE Dur='d'
GROUP BY Mat,Dur
HAVING COUNT(*)=2|||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.

daoud97@.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.|||SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1|||markc600@.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 :

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

perhaps, i d'ont expline vers well !|||daoud97@.yahoo.com schreef:

> markc600@.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 :
> 10000d
> 16030d
> 31217d
> 36000d (??)
> 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.|||daoud97@.yahoo.com schreef:

> daoud97@.yahoo.com schreef:
> > markc600@.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 :
> > 10000d
> > 16030d
> > 31217d
> > 36000d (??)
> > 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.|||
SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
AND MAX(Dur)='d'|||markc600@.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:
16030d
16030d
31217d
10000d
12000i
10000d
31217d
35000d
36000i
35000d
35000d
36000i
45d
45d
45i
250d
250i
10i
10i

result with your query :
10000d
16030d
31217d

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

why COUNT(*)=2 ?|||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'|||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
..
<daoud97@.yahoo.com> wrote in message
news:1149251877.853856.250370@.y43g2000cwc.googlegr oups.com...

daoud97@.yahoo.com schreef:

> markc600@.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.|||markc600@.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