Re: can someone help me to make a sql more pretty and more concise?

Yi Zhao wrote:
> I want to select some column(a, b) from the table with the specified
> condition, so, i can do like this:
> select a, b from mytable where id = (select id from temptable where
> tname = 'df' ) and stype = 'def' and range = 'afk'
>
How about;

SELECT a, b, count(1), sum(c) FROM mytable WHERE id = (select id from
temptable where
tname = 'df' ) and stype = 'def' and range = 'afk' GROUP BY 1,2;

Russell.
> !!!!but, I want the result contains a sum(c) and a count value extra,
> so, I use the sql below:
> select a, b,
>     (select count(1) from mytable where id =
>       (
>         select id from temptable where tname = 'df'
>       ) and stype = 'def' and range = 'afk'
>     ),
>     (select sum(c) from mytable where id =
>       (
>         select id from temptable where tname = 'df'
>       ) and stype = 'def' and range = 'afk'
>     )
>   from mytable where id = (
>     select id from temptable where tname = 'df'
>   ) and stype = 'def' and range = 'afk';
>
> can someone help me to make this sql statement above more pretty and more concise?
>
>
>