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
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 month=@month) 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)) 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
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 (c.monthly_price + (c.exceed_price * IF
(c.bandwidth_limit-@total_bandwidth <
0,@total_bandwidth-c.bandwidth_limit,0))) FROM feed_usage fu INNER JOIN
customers c ON fu.customerID=c.customerID WHERE
c.customerID=@customerID AND fu.month=@month GROUP BY fu.month,
fu.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)) 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, SGreen@stripped 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 <mysql@stripped> 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, 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.
> <snip>
>>>> 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
> <snip>
