Um, thast exactly right each select is a list of results , i want to
merge them then manipulate the data after putting them into a view,
maybe a temp table is needed for this but i dont really want to do an
entire create table statement aswell :\
On 29/12/2005, at 2:48 AM, SGreen@stripped wrote:
> You seem to be coming at SQL with a COBOL perspective. Views are
> something
> you typically create just once and they stay updated automatically.
> They
> work like tables not like queries. Assigning variables to each column
> of a
> view doesn't make any sense (in the SQL sense of "view") as each column
> could potentially contain several million values, depending on how many
> rows you identify for your VIEW in your definition statement.
>
> You demonstrated your situation with a conditional branch on a value to
> produce one of two different . What I think you wanted to do was to
> UNION
> two queries together so that you could combine the history of a
> customer's
> account with the current month's activity into a single set of results.
>
> Your queries are also insensitive to year changes. What happens when
> you
> are in Jan 2006 and you need to review the previous month's data?
>
> Since you are using views I know you are on v5.x or higher. However, I
> think you have become so entangled with your subqueries that you have
> managed to confuse yourself. Using a subquery to calculate every value
> of
> each row is VERY inefficient and unless you are actually trying to
> prove
> just how bad it is, I strongly suggest you modify your approach to
> become
> more linear, perhaps completely linear.
>
> Here is an example of how to combine two queries into a single result:
>
> (
> SELECT fu.customer_id
> , fu.month
> , sum(fu.usage) total_bandwidth
> , sum(if(p.producerID is NULL, 0, fu.usage)) feed_bandwidth
> , sum(fu.usage)/sum(if(p.producerID is NULL, 0, fu.usage)) percentage
> , count(distinct f.feedID) count
> , count(distinct if(p.producerID is null, null, f.feedid) feed_count
> FROM feed_usage fu
> INNER JOIN customers c
> ON fu.customerID=c.customerID
> INNER JOIN feeds f
> ON fu.feedID=f.feedID
> LEFT JOIN producers_join p
> ON f.feedID = p.feedID
> AND p.producerID IN (3)
> GROUP BY fu.month,fu.customerID
> )
> UNION
> (
> SELECT customerid
> , month
> , total_bandwidth
> , feed_bandwidth
> , percentage
> , count
> , feed_count
> FROM month_totals
> )
> ORDER BY fu.month DESC;
>
> See how I disentangled so many of your subqueries? If you are having
> speed
> problems, we can work on that after we get the query working, OK?
> ALSO(!)
> you don't refer to a value in the outer query by its variable name
> (because you normally don't use variables in subqueries), you normally
> use
> the actual column name in the subquery.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
> Dan Rossi <mysql@stripped> wrote on 12/28/2005 01:10:32 AM:
>
>> I have an unfinished query, i am trying to test, basically im required
>> to get the value of the current field in a row and use it for a
>> subquery in that row :| Its not a working query, and im not asking for
>> someone to fix it, however as u can see i need to send the customerID
>> and month to the sub query. What its actually trying to do is tedious
>> to explain, but i have two tables of media usage for a customer, the
>> current month will be in the usage table, so that if the plan changes
>> in that month so does the totals, but for the previous months there is
>> a static month_totals table showing just the totals recorded. So im
>> needing to select two different tables depending on what month is
>> being
>> selected. I hope this helps.
>>
>> SELECT SQL_CACHE
>>
>> CASE WHEN MONTH(mt.month) = MONTH(NOW())
>>
>> THEN
>>
>> CREATE VIEW current_month AS
>>
>> SELECT
>>
>> @customerID:=c.customerID, @month:=fu.month AS month,
>> DATE_FORMAT(fu.month,'%M') AS month_long,
>>
>> @total_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
>> fu.customerID=@customerID AND fu.month=@month),
>>
>> @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
>> fu.customerID=@customerID AND fu.month=@month AND fu.feedID IN (SELECT
>> feedID FROM producers_join WHERE producerID IN (3))),
>>
>> @percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage,
>>
>> @month_totals:=(SELECT month_total FROM month_totals WHERE
>> customerID=@customerID AND DATE_FORMAT(month,"%m")=@month GROUP BY
>> month, customerID) AS month_totals,
>>
>> @count:=(SELECT count(*) FROM feed_usage WHERE customerID=@customerID
>> AND month=@month),
>>
>> @feed_count:=(SELECT count(*) FROM feed_usage WHERE
>> customerID=@customerID AND month=@month AND feedID IN (SELECT feedID
>> FROM producers_join WHERE producerID IN (3))),
>>
>> ROUND(( IF(c.bandwidth_limit=0,
>> ((c.monthly_price/@count)*f.percentage_paid)*@feed_count,
>> ((@month_totals*@percentage)*f.percentage_paid) )),0) AS providers_cut
>>
>> FROM feed_usage fu INNER JOIN customers c ON
>> fu.customerID=c.customerID
>> INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT
>> feedID FROM producers_join WHERE producerID IN (3)) GROUP BY
>> fu.month,fu.customerID ORDER BY fu.month DESC
>>
>> ELSE
>>
>> CREATE VIEW previous_months AS
>>
>> SELECT
>>
>> @customerID:=c.customerID, @month:=MONTH(mt.month) AS month,
>> DATE_FORMAT(mt.month,'%M') AS month_long, @feeds:=feeds,
>>
>> @total_bandwidth:=( SELECT bandwidth FROM month_totals WHERE
>> customerID=@customerID AND MONTH(month)=@month),
>>
>> @feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN
>> WHERE
>> customerID=@customerID AND fu.month=@month AND fu.feedID IN (SELECT
>> feedID FROM producers_join WHERE producerID IN (3))),
>>
>> @percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage,
>>
>> @month_totals:=(SELECT month_total FROM month_totals WHERE
>> customerID=@customerID AND DATE_FORMAT(month,"%m")=@month GROUP BY
>> month, customerID) AS month_totals,
>>
>> @count:=(SELECT count(*) FROM feeds WHERE feedID IN (@feeds)),
>>
>> @feed_count:=(SELECT count(*) FROM month_totals WHERE
>> customerID=@customerID AND month=@month AND feedID IN (SELECT feedID
>> FROM producers_join WHERE producerID IN (3))),
>>
>> ROUND(( IF(c.bandwidth_limit=0,
>> ((c.monthly_price/@count)*f.percentage_paid)*@feed_count,
>> ((@month_totals*@percentage)*f.percentage_paid) )),0) AS providers_cut
>>
>> FROM feed_usage fu INNER JOIN customers c ON
>> fu.customerID=c.customerID
>> INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT
>> feedID FROM producers_join WHERE producerID IN (3)) GROUP BY
>> fu.month,fu.customerID ORDER BY fu.month DESC
>>
>> END
>>
>> On 28/12/2005, at 4:47 PM, SGreen@stripped wrote:
>>
>>> Dan Rossi <mysql@stripped> wrote on 12/27/2005 11:39:57 PM:
>>>
>>>> Hi there i am trying to use usewr variables in a select statement to
>>>> add to a where clause in a sub query. Ie
>>>>
>>>> select @id:=id,@month:=month, (select SUM(totals) from table where
>>>> id=@id and month=@month) as totals from table
>>>>
>>>> its happened on other occasions ie with calculations and sums, whats
>>>> happened in mysql5 ? It used to work in mysql4 , something i am
>>>> doing
>>>> is wrong ? Please let me know thanks.
>>>>
>>>>
>>>
>>> Is there a great reason why you are using a subquery? I could rewrite
>>> this
>>> to avoid the subquery and probably eliminate your particular problem:
>>>
>>> SELECT id, month, sum(totals) totals FROM TABLE group by id, month;
>>>
>>> Unless(!) you oversimplified your original example. In which case,
>>> you
>>> should post your actual query and I can give you a better response.
>>>
>>> Technically, the values of the variables should not be determined
> until
>>> AFTER the row is processed which means that you shouldn't be able to
>>> use
>>> them for your subquery (at least that's how I remember the SQL:2003
>>> spec
>>> but it's late and I could very well be wrong in my recollection)
>>>
>>> Personally, I am not that big a fan of subqueries anyway. There are a
>>> few
>>> types of queries where they make the SQL to achieve a result rather
>>> compact and elegant. However, I have never seen a subquery actually
>>> outperform a properly constructed linear query. They sometimes match
>>> linear performance but most often perform worse to much worse.
>>>
>>> Shawn Green
>>> Database Administrator
>>> Unimin Corporation - Spruce Pine
>>>
>>>
>>
