You may still be able to do this as a single query:
SELECT
SUM(if(field1='myvalue',1,0)) as bigTotal,
SUM(if(category1 = 'myvalue',1,0)) as categoryTotal,
SUM(if(category1 =
'myvalue',1.0,0))/SUM(if(field1='myvalue',1.0,0)) as CategoryPercent
FROM table
It performs a table scan to calculate your values but it is one way to
combine your statements into a single query.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Jacques Jocelyn <[EMAIL PROTECTED]> wrote on 10/08/2004 03:48:26
AM:
>
> Hello there,
>
> Until I had query time restriction due to my web hoster. Meaning
> whenever the query was too long, it gets killed !
> Now I have changed my webhoster, I'd like to optimize my queries and I
> would like to have your opinion on that.
> I wrote multiple queries to prevent any long query time duration such
> as :
> until now I did the following
> - to obtain the total of item which match requirements
> -> select count(*) 'bigTotal' from table where field1='myvalue'
>
> then I selected total of category from the same table to get the
> percentage of the total
> such as :
> -> select count(*) 'categoryTotal' from table where category1='myvalue'
> and from a script I calculated my percentage = bigToal/categoryTotal
>
> -> select count(*) 'categoryTotal' from table where category2='myvalue'
> and from a script I calculated my percentage = bigToal/categoryTotal
> etc..
>
> now, I have planned the following : create ONE query to do all this.
> is there a way then to merge the two previous queries in only one ?
>
> and calculate the percentage at the same time ?
> To merge all my category queries, I can use a GROUP BY, but what
> about the bigTotal, can have that in the same query ?
>
> Please advise.
> Thanks
>
>
> Best regards,
> Jacques Jocelyn
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
