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 <[EMAIL PROTECTED]> 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
> [EMAIL PROTECTED] AND [EMAIL PROTECTED]),
>
> @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu
WHERE
> [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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
> [EMAIL PROTECTED] AND DATE_FORMAT(month,"%m")[EMAIL PROTECTED] GROUP BY
> month, customerID) AS month_totals,
>
> @count:=(SELECT count(*) FROM feed_usage WHERE
[EMAIL PROTECTED]
> AND [EMAIL PROTECTED]),
>
> @feed_count:=(SELECT count(*) FROM feed_usage WHERE
> [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED],
> ((@[EMAIL PROTECTED])*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
> [EMAIL PROTECTED] AND MONTH(month)[EMAIL PROTECTED]),
>
> @feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN
WHERE
> [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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
> [EMAIL PROTECTED] AND DATE_FORMAT(month,"%m")[EMAIL PROTECTED] GROUP BY
> month, customerID) AS month_totals,
>
> @count:=(SELECT count(*) FROM feeds WHERE feedID IN (@feeds)),
>
> @feed_count:=(SELECT count(*) FROM month_totals WHERE
> [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED],
> ((@[EMAIL PROTECTED])*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, [EMAIL PROTECTED] wrote:
>
> > Dan Rossi <[EMAIL PROTECTED]> 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
> >> [EMAIL PROTECTED] and [EMAIL PROTECTED]) 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
> >
> >
>
