Thanks for your kind words of opinion, if you feel you have a better
way please do go ahead , i am going to show you the sql i ended up
using which was a union to append the current summary at the end, i
then had to use php afterwards to add up the totals as i was getting
unexpected results when grouping by month as it tended to play with the
calculations.
Most of the variables setup are for ease of reading because its such a
huge query, i could have easily put the queries into the appropriate
places, but i still for instance am required to send say the
@customerID variable to the sub queries to return a sum of results,
mind you it is not at all possible to do joins for any of this, i was
needing to get certain values and caulcations i could not obtain from a
sum, group, join of each row.
If you think i am an idiot go ahead say so as you already are, im self
taught and still learning 6 years later .. Im not perfect and there is
always room for improvment hence why Ive posted to the list for
help/recommendations or else I usually never post.
(SELECT @customerID:=c.customerID, @month:=fu.month AS month,
DATE_FORMAT(fu.stats_date,'%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 [EMAIL PROTECTED]) 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)) AND
fu.month!=DATE_FORMAT(NOW(),'%m%y') GROUP BY fu.month,fu.customerID
ORDER BY fu.month DESC)
UNION
(SELECT @customerID:=c.customerID, @month:=fu.month AS month,
DATE_FORMAT(fu.stats_date,'%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 (c.monthly_price + (c.exceed_price * IF
([EMAIL PROTECTED] <
0,@total_bandwidth-c.bandwidth_limit,0))) FROM feed_usage fu INNER JOIN
customers c ON fu.customerID=c.customerID WHERE
[EMAIL PROTECTED] AND [EMAIL PROTECTED] GROUP BY fu.month,
fu.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)) AND
fu.month=DATE_FORMAT(NOW(),'%m%y') GROUP BY fu.month,fu.customerID
ORDER BY fu.month DESC)
On 29/12/2005, at 5:19 PM, [EMAIL PROTECTED] wrote:
Dan,
You need to shoot your SQL tutor. Whoever taught you to write aggregate
queries seriously took your money. You DO NOT need to use subqueries
to do
what you want to do. You do not need to write a full CREATE TABLE
statement to create a temporary table (see other response). You do not
need a FUNCTION or a STORED PROCEDURE or a VIEW.
Please, get back to the basics. Re-read the appropriate parts of the
manual and stop trying to make this harder than it should be. At most,
this will take anywhere from 2 to 5 statements. You seem to have
become so
impressed with subqueries that you are trying to make a square peg fit
into a round hole. IMHO using subqueries is not the optimal, effective,
preferred, recommended, or suggested way to write this query.
I am more than willing to help you to refactor your query and I am sure
there will be others on this list if you don't want to deal with me any
more. But please take my advice and take the simpler, more direct
approach.
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Dan Rossi <[EMAIL PROTECTED]> wrote on 12/28/2005 11:55:35 PM:
I just tried to create a Function or Stored Procedure instead of
making
variables but it didnt even let me do this
<snip>
On 29/12/2005, at 3:31 PM, Dan Rossi wrote:
Btwi dont want the column of a view to be a variable, i think thats
what it thinks ! Im just needing to send the value of the current
primary key field top a sub query !
Read my latest post if i can get around not using variables, and
still
manage to get the right values of a current row going to a sub query
?
On 29/12/2005, at 2:48 AM, [EMAIL PROTECTED] 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.
<snip>
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
<snip>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
