MAX

I have the following code that I created today:
select month(DataPesquisa) as Mes, count(FConceito) as Bom,
isnull(Excelente.Conceito,0) as Excelente,isnull(Regular.Conceito,0) as
Regular,
isnull(Ruim.Conceito,0) as Ruim
from satisfacao x
left outer join (select referencia, count(Fconceito) as Conceito from
satisfacao
where month(Datapesquisa) = '01' and FConceito = '4' and referencia = '2005'
group by referencia) Excelente
on x.referencia = Excelente.referencia
left outer join (select referencia, count(Fconceito) as Conceito from
satisfacao
where month(Datapesquisa) = '01' and FConceito = '2' and referencia = '2005'
group by referencia) Regular
on x.referencia = Regular.referencia
left outer join (select referencia, count(Fconceito) as Conceito from
satisfacao
where month(Datapesquisa) = '01' and FConceito = '1' and referencia = '2005'
group by referencia) Ruim
on x.referencia = Ruim.referencia
where month(Datapesquisa) = '01' and FConceito = '3'and x.referencia =
'2005'
group by x.referencia, month(x.DataPesquisa), Excelente.Conceito,
Regular.Conceito, Ruim.Conceito
Your result is:
Excellent Godd Regulate Bad
-- -- -- -- --
1 3 2 1 0
My question: I want to select the LARGEST value of these results. Does give
there for using the function MAX in that procedure of top or will I have to
do this for the application (programming even)'?I think you can hadle it in the client application
Madhivanan|||Frank Dulk wrote:

> select month(DataPesquisa) as Mes, count(FConceito) as Bom,
> isnull(Excelente.Conceito,0) as Excelente,isnull(Regular.Conceito,0) as
> Regular,
> isnull(Ruim.Conceito,0) as Ruim
> from satisfacao x
> left outer join (select referencia, count(Fconceito) as Conceito from
> satisfacao
> where month(Datapesquisa) = '01' and FConceito = '4' and referencia = '200
5'
> group by referencia) Excelente
> on x.referencia = Excelente.referencia
> left outer join (select referencia, count(Fconceito) as Conceito from
> satisfacao
> where month(Datapesquisa) = '01' and FConceito = '2' and referencia = '200
5'
> group by referencia) Regular
> on x.referencia = Regular.referencia
> left outer join (select referencia, count(Fconceito) as Conceito from
> satisfacao
> where month(Datapesquisa) = '01' and FConceito = '1' and referencia = '200
5'
> group by referencia) Ruim
> on x.referencia = Ruim.referencia
> where month(Datapesquisa) = '01' and FConceito = '3'and x.referencia =
> '2005'
> group by x.referencia, month(x.DataPesquisa), Excelente.Conceito,
> Regular.Conceito, Ruim.Conceito
Instead of several self joins you can rewrite that query to a single
aggregation:
select
month(DataPesquisa) as Mes,
sum(case when FConceito = '4' then 1 else 0) as Excelente,
sum(case when FConceito = '3' then 1 else 0) as Bom,
sum(case when FConceito = '2' then 1 else 0) as Regular,
sum(case when FConceito = '1' then 1 else 0) as Ruim,
from satisfacao
where month(Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa)

> Excellent Godd Regulate Bad
> -- -- -- -- --
> 1 3 2 1 0
>
> My question: I want to select the LARGEST value of these results. Does giv
e
> there for using the function MAX in that procedure of top or will I have t
o
> do this for the application (programming even)'?
If you want to add a new column with that max value, then the easiest
way is doing it on the client. Else you have to add another case:
select
dt.*,
case
when Excelente > Bom and Excelente > Regular and Excelente > Ruim
then Excelente
when Bom > Regular and Bom > Ruim then Bom
when Regular > Ruim then Regular
else Ruim
end
from
(
select
month(DataPesquisa) as Mes,
sum(case when FConceito = '4' then 1 else 0) as Excelente,
sum(case when FConceito = '3' then 1 else 0) as Bom,
sum(case when FConceito = '2' then 1 else 0) as Regular,
sum(case when FConceito = '1' then 1 else 0) as Ruim,
from satisfacao
where month(Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa)
) dt
If you just need that max info:
select
month(DataPesquisa) as Mes,
max(cnt)
from
(
select
month(DataPesquisa) as Mes,
FConceito,
count(*) as cnt
from satisfacao
where month(Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa), FConceito
) dt
all queries untested...
Dieter|||Thank you for the help
I used your code making the necessary fittings and I have new question.
After arranging, Query was like this:
select
dt.*,
case
when Excelente > Bom and Excelente > Regular and Excelente > Ruim
then Excelente
when Bom > Regular and Bom > Ruim then Bom
when Regular > Ruim then Regular
else Ruim
end
as Maior
from (
select month(DataPesquisa) as Mes,
sum (case DConceito When '4' then 1 else 0 End) as Excelente,
sum (case DConceito When '3' then 1 else 0 End) as Bom,
sum (case DConceito When '2' then 1 else 0 End) as Regular,
sum (case DConceito When '1' then 1 else 0 End) as Ruim
from satisfacao x
where month(x.Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa)
) dt
results it is it:
Mes Excelente Bom Regular Ruim Maior
Now: Does have as I place in Adult's place the name of the field that the
largest value is (Good or Bad)?
"Dieter Noeth" <dnoeth@.gmx.de> escreveu na mensagem
news:Ou28y4vHFHA.2984@.TK2MSFTNGP15.phx.gbl...
> Frank Dulk wrote:
>
'2005'
'2005'
'2005'
> Instead of several self joins you can rewrite that query to a single
> aggregation:
> select
> month(DataPesquisa) as Mes,
> sum(case when FConceito = '4' then 1 else 0) as Excelente,
> sum(case when FConceito = '3' then 1 else 0) as Bom,
> sum(case when FConceito = '2' then 1 else 0) as Regular,
> sum(case when FConceito = '1' then 1 else 0) as Ruim,
> from satisfacao
> where month(Datapesquisa) = '01' and x.referencia = '2005'
> group by x.referencia, month(x.DataPesquisa)
>
give
to
> If you want to add a new column with that max value, then the easiest
> way is doing it on the client. Else you have to add another case:
> select
> dt.*,
> case
> when Excelente > Bom and Excelente > Regular and Excelente > Ruim
> then Excelente
> when Bom > Regular and Bom > Ruim then Bom
> when Regular > Ruim then Regular
> else Ruim
> end
> from
> (
> select
> month(DataPesquisa) as Mes,
> sum(case when FConceito = '4' then 1 else 0) as Excelente,
> sum(case when FConceito = '3' then 1 else 0) as Bom,
> sum(case when FConceito = '2' then 1 else 0) as Regular,
> sum(case when FConceito = '1' then 1 else 0) as Ruim,
> from satisfacao
> where month(Datapesquisa) = '01' and x.referencia = '2005'
> group by x.referencia, month(x.DataPesquisa)
> ) dt
>
> If you just need that max info:
> select
> month(DataPesquisa) as Mes,
> max(cnt)
> from
> (
> select
> month(DataPesquisa) as Mes,
> FConceito,
> count(*) as cnt
> from satisfacao
> where month(Datapesquisa) = '01' and x.referencia = '2005'
> group by x.referencia, month(x.DataPesquisa), FConceito
> ) dt
>
> all queries untested...
> Dieter