[ENG] count() the same field in a table depending on many tests

Hello people,

I've a simple table wich contains an ID, a pricefrom and a priceto.

I want to count the different "segments" of price search by doing those
queries:

SELECT count(idsearchlog) from searchlog where priceto <500;
SELECT count(idsearchlog) from searchlog where pricefrom >=500 and priceto <
1000;
SELECT count(idsearchlog) from searchlog where pricefrom >= 1000;

Now I know I can to a "union", but I'd like to know if there is a better and
faster query ?

(SELECT count(idsearchlog) from searchlog where priceto <500)
union
(SELECT count(idsearchlog) from searchlog where pricefrom >=500 and priceto
< 1000)
union
(SELECT count(idsearchlog) from searchlog where pricefrom >= 1000)

Thanks for helping.

Bob

Re: [ENG] count() the same field in a table depending on many tests

Bob Bedford wrote:
>
> I want to count the different "segments" of price search by doing those
> queries:
>
> SELECT count(idsearchlog) from searchlog where priceto <500;
> SELECT count(idsearchlog) from searchlog where pricefrom >=500 and priceto <
> 1000;
> SELECT count(idsearchlog) from searchlog where pricefrom >= 1000;
>
> Now I know I can to a "union", but I'd like to know if there is a better and
> faster query ?
>
> (SELECT count(idsearchlog) from searchlog where priceto <500)
> union
> (SELECT count(idsearchlog) from searchlog where pricefrom >=500 and priceto
> < 1000)
> union
> (SELECT count(idsearchlog) from searchlog where pricefrom >= 1000)

You could try something like this (untested):
SELECT COUNT(*),
CASE WHEN priceto < 500 THEN 1 ELSE
CASE WHEN pricefrom >=500 and priceto < 1000 THEN 2 ELSE
CASE WHEN pricefrom >= 1000 THEN 3 ELSE 0 END
END
END AS type
FROM searchlog
GROUP BY 2

You'll have to try for yourself whether this is faster...


Grüße,
Dominik
--
MonstersGame - Die Schlacht zwischen Vampiren und Werwölfen
http://spielwelt6.monstersgame.net/?ac=vid&vid=3018786

Re: [ENG] count() the same field in a table depending on many tests

"Dominik Echterbruch" a écrit dans le message de
news: 1138208488.67897.0@doris.uk.clara.net...
> Bob Bedford wrote:
>>
>> I want to count the different "segments" of price search by doing those
>> queries:
>>
>> SELECT count(idsearchlog) from searchlog where priceto <500;
>> SELECT count(idsearchlog) from searchlog where pricefrom >=500 and
>> priceto < 1000;
>> SELECT count(idsearchlog) from searchlog where pricefrom >= 1000;
>>
>> Now I know I can to a "union", but I'd like to know if there is a better
>> and faster query ?
>>
>> (SELECT count(idsearchlog) from searchlog where priceto <500)
>> union
>> (SELECT count(idsearchlog) from searchlog where pricefrom >=500 and
>> priceto < 1000)
>> union
>> (SELECT count(idsearchlog) from searchlog where pricefrom >= 1000)
>
> You could try something like this (untested):
> SELECT COUNT(*),
> CASE WHEN priceto < 500 THEN 1 ELSE
> CASE WHEN pricefrom >=500 and priceto < 1000 THEN 2 ELSE
> CASE WHEN pricefrom >= 1000 THEN 3 ELSE 0 END
> END
> END AS type
> FROM searchlog
> GROUP BY 2
>
> You'll have to try for yourself whether this is faster...
>
>
> Grüße,
> Dominik

WOHAOOO !!!! your query is quite 2 times faster !!!! great thanks for the
help Dominik !!!

Bob

Re: [ENG] count() the same field in a table depending on many tests

Dominik Echterbruch wrote:

> You could try something like this (untested):
> SELECT COUNT(*),
> CASE WHEN priceto < 500 THEN 1 ELSE
> CASE WHEN pricefrom >=500 and priceto < 1000 THEN 2 ELSE
> CASE WHEN pricefrom >= 1000 THEN 3 ELSE 0 END
> END
> END AS type
> FROM searchlog
> GROUP BY 2

A single CASE is enough :-)

CASE
WHEN priceto < 500 THEN 1
WHEN priceto < 1000 THEN 2
ELSE 0 END
END AS type

Dieter

Re: [ENG] count() the same field in a table depending on many tests

Dieter Noeth wrote:
> Dominik Echterbruch wrote:
>
>> You could try something like this (untested):
>
> A single CASE is enough :-)
>
> CASE
> WHEN priceto < 500 THEN 1
> WHEN priceto < 1000 THEN 2
> ELSE 0 END
> END AS type

That's right. But I wanted to keep the terminology of his WHERE clauses
and avoid false positives. And I was too lazy to think it over ;)


Grüße,
Dominik
--
MonstersGame - Die Schlacht zwischen Vampiren und Werwölfen
http://spielwelt6.monstersgame.net/?ac=vid&vid=3018786