|
Home > Archive > MySQL ODBC Connector > December 2005 > need help with user variables in where clause of sub query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
need help with user variables in where clause of sub query
|
|
| Dan Rossi 2005-12-28, 3:23 am |
| 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:=mont
h, (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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-12-28, 3:23 am |
| --=_alternative 001F980D852570E5_=
Content-Type: text/plain; charset="US-ASCII"
Dan Rossi <mysql@electroteque.org> 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:=mont
h, (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
--=_alternative 001F980D852570E5_=--
| |
| Dan Rossi 2005-12-28, 3:23 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=@customer
ID AND fu.month=@month),
@feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
fu. customerID=@customer
ID 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:=(SELE
CT month_total FROM month_totals WHERE
customerID=@customer
ID AND DATE_FORMAT(month,"%m")=@month GROUP BY
month, customerID) AS month_totals,
@count:=(SELECT count(*) FROM feed_usage WHERE customerID=@customer
ID
AND month=@month),
@feed_count:=(SELECT
count(*) FROM feed_usage WHERE
customerID=@customer
ID 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)*@fe
ed_count,
((@month_totals*@per
centage)*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=@customer
ID AND MONTH(month)=@month)
,
@feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN WHERE
customerID=@customer
ID 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:=(SELE
CT month_total FROM month_totals WHERE
customerID=@customer
ID 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=@customer
ID 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)*@fe
ed_count,
((@month_totals*@per
centage)*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@unimin.com wrote:
> Dan Rossi <mysql@electroteque.org> wrote on 12/27/2005 11:39:57 PM:
>
>
> 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
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-12-28, 11:23 am |
| --=_alternative 0056C046852570E5_=
Content-Type: text/plain; charset="US-ASCII"
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@electroteque.org> 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=@customer
ID AND fu.month=@month),
>
> @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
> fu. customerID=@customer
ID 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:=(SELE
CT month_total FROM month_totals WHERE
> customerID=@customer
ID AND DATE_FORMAT(month,"%m")=@month GROUP BY
> month, customerID) AS month_totals,
>
> @count:=(SELECT count(*) FROM feed_usage WHERE customerID=@customer
ID
> AND month=@month),
>
> @feed_count:=(SELECT
count(*) FROM feed_usage WHERE
> customerID=@customer
ID 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)*@fe
ed_count,
> ((@month_totals*@per
centage)*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=@customer
ID AND MONTH(month)=@month)
,
>
> @feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN WHERE
> customerID=@customer
ID 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:=(SELE
CT month_total FROM month_totals WHERE
> customerID=@customer
ID 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=@customer
ID 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)*@fe
ed_count,
> ((@month_totals*@per
centage)*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@unimin.com wrote:
>
until[color=darkred]
>
--=_alternative 0056C046852570E5_=--
| |
| Dan Rossi 2005-12-29, 3:24 am |
| 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@unimin.com wrote:
[color=darkred]
> 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@electroteque.org> wrote on 12/28/2005 01:10:32 AM:
>
> until
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Dan Rossi 2005-12-29, 3:24 am |
| --Apple-Mail-1-757203435
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=ISO-8859-1;
format=flowed
Btwi dont want the column of a view to be a variable, i think thats=20
what it thinks ! Im just needing to send the value of the current=20
primary key field top a sub query !
Read my latest post if i can get around not using variables, and still=20=
manage to get the right values of a current row going to a sub query ?
On 29/12/2005, at 2:48 AM, SGreen@unimin.com wrote:
>
> You seem to be coming at SQL with a COBOL perspective. Views are=20
> something you typically create just once and they stay updated=20
> automatically. They work like tables not like queries. Assigning=20
> variables to each column of a view doesn't make any sense (in the SQL=20=
> sense of "view") as each column could potentially contain several=20
> million values, depending on how many rows you identify for your VIEW=20=
> in your definition statement.
>
> You demonstrated your situation with a conditional branch on a value=20=
> to produce one of two different . What I think you wanted to do was to=20=
> UNION two queries together so that you could combine the history of a=20=
> customer's account with the current month's activity into a single set=20=
> of results.
>
> Your queries are also insensitive to year changes. What happens when=20=
> 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=20=
> think you have become so entangled with your subqueries that you have=20=
> managed to confuse yourself. Using a subquery to calculate every value=20=
> of each row is VERY inefficient and unless you are actually trying to=20=
> prove just how bad it is, I strongly suggest you modify your approach=20=
> 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
> =A0 , =A0fu.month
> =A0 , sum(fu.usage) total_bandwidth
> =A0 , sum(if(p.producerID is NULL, 0, fu.usage)) feed_bandwidth
> =A0 , sum(fu.usage)/sum(if(p.producerID is NULL, 0, fu.usage)) =
percentage
> =A0 , count(distinct f.feedID) count
> =A0 , count(distinct if(p.producerID is null, null, f.feedid) =
feed_count
> FROM feed_usage fu
> INNER JOIN customers c
> =A0 ON fu.customerID=3Dc.customerID
> INNER JOIN feeds f
> =A0 ON fu.feedID=3Df.feedID
> LEFT JOIN producers_join p
> =A0 ON f.feedID =3D p.feedID
> =A0 AND p.producerID IN (3)
> GROUP BY fu.month,fu.customerID
> )
> UNION
> (
> SELECT customerid
> =A0 , =A0month
> =A0 , total_bandwidth
> =A0 , feed_bandwidth
> =A0 , percentage
> =A0 , count
> =A0 , feed_count
> FROM month_totals
> )
> ORDER BY fu.month DESC;
>
> See how I disentangled so many of your subqueries? If you are having=20=
[color=darkred]
> speed problems, we can work on that after we get the query working,=20
> OK? ALSO(!) you don't refer to a value in the outer query by its=20
> variable name (because you normally don't use variables in=20
> subqueries), you normally use the actual column name in the subquery.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
> Dan Rossi <mysql@electroteque.org> wrote on 12/28/2005 01:10:32 AM:
>
> required
[color=darkred]
> for
> customerID
> tedious
the[color=darkred]
> changes
[color=darkred]
> is
[color=darkred]
> being
[color=darkred]
> WHERE
WHERE[color=darkred]
[color=darkred]
> (SELECT
BY[color=darkred]
> customerID=3D@custom
erID
feedID[color=darkred
]
> providers_cut
> fu.customerID=3Dc.customerID
(SELECT[color=darkre
d]
[color=darkred]
> =A0WHERE
(SELECT[color=darkre
d]
BY[color=darkred]
feedID[color=darkred
]
> providers_cut
> fu.customerID=3Dc.customerID
(SELECT[color=darkre
d]
[color=darkred]
> PM:
> statement to
[color=darkred]
> where
[color=darkred]
> whats
[color=darkred]
> doing
> rewrite
> problem:
> month;
> case, you
> response.
[color=darkred]
> until
[color=darkred]
> to
> SQL:2003
[color=darkred]
> are a
> rather
> actually
> match
--Apple-Mail-1-757203435--
| |
| Dan Rossi 2005-12-29, 3:24 am |
| I just tried to create a Function or Stored Procedure instead of making=20=
variables but it didnt even let me do this
CREATE FUNCTION test (customerID, month, producerID)
RETURN SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE=20
fu. customerID=3Dcustome
rID AND fu.month=3Dmonth AND fu.feedID IN (SELECT=20=
feedID FROM producers_join WHERE producerID IN (producerID))
something like that, so if functions will work instead of variables for=20=
sub queries and views, im still needing to send the primary key of the=20=
current row to them somehow.
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=20
> what it thinks ! Im just needing to send the value of the current=20
> primary key field top a sub query !
>
> Read my latest post if i can get around not using variables, and still=20=
[color=darkred]
> manage to get the right values of a current row going to a sub query ?
>
> On 29/12/2005, at 2:48 AM, SGreen@unimin.com wrote:
>
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
result:[color=darkre
d]
feed_count[color=dar
kred]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
im[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
GROUP BY[color=darkred]
[color=darkred]
(SELECT[color=darkre
d]
[color=darkred]
(SELECT[color=darkre
d]
GROUP BY[color=darkred]
[color=darkred]
(SELECT[color=darkre
d]
[color=darkred]
table=20[color=darkr
ed]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-12-29, 3:24 am |
| --=_alternative 0022999C852570E6_=
Content-Type: text/plain; charset="US-ASCII"
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@electroteque.org> 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>[color=darkred]
>
> On 29/12/2005, at 3:31 PM, Dan Rossi wrote:
>
[color=darkred]
[color=darkred]
[color=darkred]
<snip>[color=darkred]
[color=darkred]
im[color=darkred]
(SELECT[color=darkre
d]
<snip>
--=_alternative 0022999C852570E6_=--
| |
| Dan Rossi 2005-12-29, 7:23 am |
| 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=@customer
ID AND fu.month=@month),
@feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
fu. customerID=@customer
ID 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:=(SELE
CT month_total FROM month_totals WHERE
customerID=@customer
ID AND month=@month) AS month_totals,
@count:=(SELECT count(*) FROM feed_usage WHERE customerID=@customer
ID
AND month=@month),
@feed_count:=(SELECT
count(*) FROM feed_usage WHERE
customerID=@customer
ID 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)*@fe
ed_count,
((@month_totals*@per
centage)*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(N
OW(),'%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=@customer
ID AND fu.month=@month),
@feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
fu. customerID=@customer
ID 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:=(SELE
CT (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=@customer
ID AND fu.month=@month GROUP BY fu.month,
fu.customerID) AS month_totals,
@count:=(SELECT count(*) FROM feed_usage WHERE customerID=@customer
ID
AND month=@month),
@feed_count:=(SELECT
count(*) FROM feed_usage WHERE
customerID=@customer
ID 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)*@fe
ed_count,
((@month_totals*@per
centage)*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(NO
W(),'%m%y') GROUP BY fu.month,fu.customerID
ORDER BY fu.month DESC)
On 29/12/2005, at 5:19 PM, SGreen@unimin.com 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@electroteque.org> wrote on 12/28/2005 11:55:35 PM:
>
> <snip>
>
>
>
> <snip>
>
> im
> (SELECT
> <snip>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-12-29, 11:23 am |
| --=_alternative 0057CD70852570E6_=
Content-Type: text/plain; charset="US-ASCII"
Dan Rossi <mysql@electroteque.org> wrote on 12/29/2005 07:19:13 AM:
> 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.
<big snip>
Dan, I said I would help and I am offering to do so. If you would rather
have someone else take over, please just say so and I will back off. This
forum is the best place I have ever found for getting all kinds of crazy
help and if you prefer someone else, I understand.
I need some information about your table designs. Would you please post
the SHOW CREATE TABLE statement results for the following tables:
feed_usage, customers, feeds, producers_join, and month_totals? For
example:
SHOW CREATE TABLE feed_usage\G
(the \G makes the output vertical, much less wrapping)
I think I understand your various table relationships as you seem to set
them up well in your example <table reference> clause.
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(N
OW(),'%m%y')
The funky numbers from your initial attempts will more than likely have
been caused by several tables being joined having multiple rows of
matching data.
Not all data-based reports can exist as single statements. It's a
limitation of the SQL language that when you want to do calculations based
on the results of aggregation (SUM(), COUNT(), AVG(), etc.) , you need a
second or more rounds of processing especially if you are aggregating
several sets of data (usage data, bandwidth data, billing data, etc.).
Temporary tables are the preferred place to store any intermediate results
as they are specific to the connection that creates them (In fact the
query engine generates at least one temporary table for every subquery you
do). I don't want you to think I am completely against subqueries, I am
not. It's just that you were not exactly using them to their full
potential.
If we keep our wits about us, we can get through this.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 0057CD70852570E6_=--
|
|
|
|
|