Re: Multiple SUM in query
I'm pretty sure that you can encourage CakePHP to do this wihtout
changing the afterFind function as follows:
$this->Test->find(
'all',
array (
'fields' => array ('test.field3', 'SUM(field1) AS
`test.Sum1`', 'SUM(field2) AS
`test.Sum2`'),
'group' => array ('test.field3')
)
);
i.e. but prefixing your fields with Model names, now this is done
automagically on regular fields, but if you use an aggregate function
then it's not. by explicitly naming your aggregate fields with the
correct model prefix it should work fine.
On Dec 2, 3:36 am, cakebaker wrote:
> Figured out what the problem was for me. I had inserted this afterFind
> () function in the app Model
>
> http://teknoid.wordpress.com/2008/09/29/dealing-with-calculated-field...
>
> so that the calculated fields show up in the appropriate models when
> returned, and not in an ambiguous array. However it was causing this
> SUM problem if there were more than one, so i found later when reading
> the comments, one of the posters had made a modification to it so that
> it would return multiples:
>
> http://teknoid.wordpress.com/2008/09/29/dealing-with-calculated-field...
>
> Solved my problem perfectly. Hope that helps someone, maybe the OP.
>
> On Nov 30, 9:23 pm, "Dr. Loboto" wrote:
>
>
>
> > Just checked on cakes 1.2.5 and 1.2.4 - all fine.
>
> > Query:
> > $this->Test->find(
> > 'all',
> > array (
> > 'fields' => array ('field3', 'SUM(field1) AS Sum1',
> > 'SUM(field2) AS
> > Sum2'),
> > 'group' => array ('field3')
> > )
> > );
>
> > Generated SQL:
> > SELECT `Test`.`field3`, SUM(field1) AS Sum1, SUM(field2) AS Sum2 FROM
> > `test` AS `Test` WHERE 1 = 1 GROUP BY field3
>
> > Result:
> > Array
> > (
> > [0] => Array
> > (
> > [Test] => Array
> > (
> > [field3] => test 1
> > )
>
> > [0] => Array
> > (
> > [Sum1] => 4
> > [Sum2] => 6
> > )
>
> > )
>
> > [1] => Array
> > (
> > [Test] => Array
> > (
> > [field3] => test 2
> > )
>
> > [0] => Array
> > (
> > [Sum1] => 5
> > [Sum2] => 6
> > )
>
> > )
> > )
>
> > Source data:
> > id field1 field2 field3
> > 1 1 2 test 1
> > 2 3 4 test 1
> > 3 5 6 test 2
>
> > On Nov 30, 7:47 am, cakebaker wrote:
>
> > > I have this same problem happening right now. You can put other fields
> > > in that are not sums and they are returned just fine, but of all the
> > > SUMs you put it it only returns the first occuring one. It is bizzarr.
> > > It is generating the appropriate SQL statement, because when i take
> > > the SQL statement it creates from the debugger, and run it in
> > > phpmyadmin manually, it returns exactly what i need, with all the
> > > appropriate sums. But for some reason it isnt returning it in cakephp.
> > > Any help on this would be great.
>
> > > On Nov 28, 6:48 am, Robin Marx wrote:
>
> > > > Since I can't seem to find the answer on google, nor in the docs, I
> > > > hope someone here can help me.
>
> > > > Whenever I try to do a query with more then 1 SUM-field in it, it will
> > > > only return the first SUM-field, not the other ones in its results.
>
> > > > So for instance :
>
> > > > SUM( amount1 ) as amountOne, SUM( amount2 ) as amountTwo + group on
> > > > year
>
> > > > will only return amountOne as result.
>
> > > > When I do :
>
> > > > SUM( amount2 ) as amountTwo, SUM( amount1 ) as amountOne + group
> > > > on year
>
> > > > it will only return amountTwo.
>
> > > > Anyone got an idea why this behaviour occurs and how to fix it?
>
> > > > We would be most grateful because it's quite urgent for a school
> > > > project.
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
Re: Multiple SUM in query
Figured out what the problem was for me. I had inserted this afterFind
() function in the app Model
http://teknoid.wordpress.com/2008/09/29/dealing-with-calculated-fields-in-cakephps-find/
so that the calculated fields show up in the appropriate models when
returned, and not in an ambiguous array. However it was causing this
SUM problem if there were more than one, so i found later when reading
the comments, one of the posters had made a modification to it so that
it would return multiples:
http://teknoid.wordpress.com/2008/09/29/dealing-with-calculated-fields-in-cakephps-find/#comment-921
Solved my problem perfectly. Hope that helps someone, maybe the OP.
On Nov 30, 9:23 pm, "Dr. Loboto" wrote:
> Just checked on cakes 1.2.5 and 1.2.4 - all fine.
>
> Query:
> $this->Test->find(
> 'all',
> array (
> 'fields' => array ('field3', 'SUM(field1) AS Sum1',
> 'SUM(field2) AS
> Sum2'),
> 'group' => array ('field3')
> )
> );
>
> Generated SQL:
> SELECT `Test`.`field3`, SUM(field1) AS Sum1, SUM(field2) AS Sum2 FROM
> `test` AS `Test` WHERE 1 = 1 GROUP BY field3
>
> Result:
> Array
> (
> [0] => Array
> (
> [Test] => Array
> (
> [field3] => test 1
> )
>
> [0] => Array
> (
> [Sum1] => 4
> [Sum2] => 6
> )
>
> )
>
> [1] => Array
> (
> [Test] => Array
> (
> [field3] => test 2
> )
>
> [0] => Array
> (
> [Sum1] => 5
> [Sum2] => 6
> )
>
> )
> )
>
> Source data:
> id field1 field2 field3
> 1 1 2 test 1
> 2 3 4 test 1
> 3 5 6 test 2
>
> On Nov 30, 7:47 am, cakebaker wrote:
>
> > I have this same problem happening right now. You can put other fields
> > in that are not sums and they are returned just fine, but of all the
> > SUMs you put it it only returns the first occuring one. It is bizzarr.
> > It is generating the appropriate SQL statement, because when i take
> > the SQL statement it creates from the debugger, and run it in
> > phpmyadmin manually, it returns exactly what i need, with all the
> > appropriate sums. But for some reason it isnt returning it in cakephp.
> > Any help on this would be great.
>
> > On Nov 28, 6:48 am, Robin Marx wrote:
>
> > > Since I can't seem to find the answer on google, nor in the docs, I
> > > hope someone here can help me.
>
> > > Whenever I try to do a query with more then 1 SUM-field in it, it will
> > > only return the first SUM-field, not the other ones in its results.
>
> > > So for instance :
>
> > > SUM( amount1 ) as amountOne, SUM( amount2 ) as amountTwo + group on
> > > year
>
> > > will only return amountOne as result.
>
> > > When I do :
>
> > > SUM( amount2 ) as amountTwo, SUM( amount1 ) as amountOne + group
> > > on year
>
> > > it will only return amountTwo.
>
> > > Anyone got an idea why this behaviour occurs and how to fix it?
>
> > > We would be most grateful because it's quite urgent for a school
> > > project.
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
Re: Multiple SUM in query
Just checked on cakes 1.2.5 and 1.2.4 - all fine.
Query:
$this->Test->find(
'all',
array (
'fields' => array ('field3', 'SUM(field1) AS Sum1',
'SUM(field2) AS
Sum2'),
'group' => array ('field3')
)
);
Generated SQL:
SELECT `Test`.`field3`, SUM(field1) AS Sum1, SUM(field2) AS Sum2 FROM
`test` AS `Test` WHERE 1 = 1 GROUP BY field3
Result:
Array
(
[0] => Array
(
[Test] => Array
(
[field3] => test 1
)
[0] => Array
(
[Sum1] => 4
[Sum2] => 6
)
)
[1] => Array
(
[Test] => Array
(
[field3] => test 2
)
[0] => Array
(
[Sum1] => 5
[Sum2] => 6
)
)
)
Source data:
id field1 field2 field3
1 1 2 test 1
2 3 4 test 1
3 5 6 test 2
On Nov 30, 7:47 am, cakebaker wrote:
> I have this same problem happening right now. You can put other fields
> in that are not sums and they are returned just fine, but of all the
> SUMs you put it it only returns the first occuring one. It is bizzarr.
> It is generating the appropriate SQL statement, because when i take
> the SQL statement it creates from the debugger, and run it in
> phpmyadmin manually, it returns exactly what i need, with all the
> appropriate sums. But for some reason it isnt returning it in cakephp.
> Any help on this would be great.
>
> On Nov 28, 6:48 am, Robin Marx wrote:
>
>
>
> > Since I can't seem to find the answer on google, nor in the docs, I
> > hope someone here can help me.
>
> > Whenever I try to do a query with more then 1 SUM-field in it, it will
> > only return the first SUM-field, not the other ones in its results.
>
> > So for instance :
>
> > SUM( amount1 ) as amountOne, SUM( amount2 ) as amountTwo + group on
> > year
>
> > will only return amountOne as result.
>
> > When I do :
>
> > SUM( amount2 ) as amountTwo, SUM( amount1 ) as amountOne + group
> > on year
>
> > it will only return amountTwo.
>
> > Anyone got an idea why this behaviour occurs and how to fix it?
>
> > We would be most grateful because it's quite urgent for a school
> > project.
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
Re: Multiple SUM in query
I have this same problem happening right now. You can put other fields in that are not sums and they are returned just fine, but of all the SUMs you put it it only returns the first occuring one. It is bizzarr. It is generating the appropriate SQL statement, because when i take the SQL statement it creates from the debugger, and run it in phpmyadmin manually, it returns exactly what i need, with all the appropriate sums. But for some reason it isnt returning it in cakephp. Any help on this would be great. On Nov 28, 6:48 am, Robin Marx wrote: > Since I can't seem to find the answer on google, nor in the docs, I > hope someone here can help me. > > Whenever I try to do a query with more then 1 SUM-field in it, it will > only return the first SUM-field, not the other ones in its results. > > So for instance : > > SUM( amount1 ) as amountOne, SUM( amount2 ) as amountTwo + group on > year > > will only return amountOne as result. > > When I do : > > SUM( amount2 ) as amountTwo, SUM( amount1 ) as amountOne + group > on year > > it will only return amountTwo. > > Anyone got an idea why this behaviour occurs and how to fix it? > > We would be most grateful because it's quite urgent for a school > project. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Re: Multiple SUM in query
See: http://book.cakephp.org/view/74/Complex-Find-Conditions CTRL+F: "How about GROUP BY?" on that page. See the MIN() usage, I'd just try to use it twice (and SUM instead of MIN) On Nov 29, 2:13 am, Dave wrote: > They really let you use PHP Frameworks for your homework? > > On Sat, Nov 28, 2009 at 3:13 PM, John Andersen wrote: > > > Please copy/paste the find statement into the post, so that we better > > may be able to see what may be the problem ;) > > Enjoy, > > John > > > On Nov 28, 3:48 pm, Robin Marx wrote: > > > Since I can't seem to find the answer on google, nor in the docs, I > > > hope someone here can help me. > > > > Whenever I try to do a query with more then 1 SUM-field in it, it will > > > only return the first SUM-field, not the other ones in its results. > > > > So for instance : > > > > SUM( amount1 ) as amountOne, SUM( amount2 ) as amountTwo + group on > > > year > > > > will only return amountOne as result. > > > > When I do : > > > > SUM( amount2 ) as amountTwo, SUM( amount1 ) as amountOne + group > > > on year > > > > it will only return amountTwo. > > > > Anyone got an idea why this behaviour occurs and how to fix it? > > > > We would be most grateful because it's quite urgent for a school > > > project. > > > Check out the new CakePHP Questions sitehttp://cakeqs.organd help others > > with their CakePHP related questions. > > > You received this message because you are subscribed to the Google Groups > > "CakePHP" group. > > To post to this group, send email to cake-php@googlegroups.com > > To unsubscribe from this group, send email to > > cake-php+unsubscr...@googlegroups.comFor > > more options, visit this group at > >http://groups.google.com/group/cake-php?hl=en Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Re: Multiple SUM in query
They really let you use PHP Frameworks for your homework? On Sat, Nov 28, 2009 at 3:13 PM, John Andersen wrote: > Please copy/paste the find statement into the post, so that we better > may be able to see what may be the problem ;) > Enjoy, >John > > On Nov 28, 3:48 pm, Robin Marx wrote: > > Since I can't seem to find the answer on google, nor in the docs, I > > hope someone here can help me. > > > > Whenever I try to do a query with more then 1 SUM-field in it, it will > > only return the first SUM-field, not the other ones in its results. > > > > So for instance : > > > > SUM( amount1 ) as amountOne, SUM( amount2 ) as amountTwo + group on > > year > > > > will only return amountOne as result. > > > > When I do : > > > > SUM( amount2 ) as amountTwo, SUM( amount1 ) as amountOne + group > > on year > > > > it will only return amountTwo. > > > > Anyone got an idea why this behaviour occurs and how to fix it? > > > > We would be most grateful because it's quite urgent for a school > > project. > > Check out the new CakePHP Questions site http://cakeqs.org and help others > with their CakePHP related questions. > > You received this message because you are subscribed to the Google Groups > "CakePHP" group. > To post to this group, send email to cake-php@googlegroups.com > To unsubscribe from this group, send email to > cake-php+unsubscr...@googlegroups.comFor > more options, visit this group at > http://groups.google.com/group/cake-php?hl=en > Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Re: Multiple SUM in query
Please copy/paste the find statement into the post, so that we better may be able to see what may be the problem ;) Enjoy, John On Nov 28, 3:48 pm, Robin Marx wrote: > Since I can't seem to find the answer on google, nor in the docs, I > hope someone here can help me. > > Whenever I try to do a query with more then 1 SUM-field in it, it will > only return the first SUM-field, not the other ones in its results. > > So for instance : > > SUM( amount1 ) as amountOne, SUM( amount2 ) as amountTwo + group on > year > > will only return amountOne as result. > > When I do : > > SUM( amount2 ) as amountTwo, SUM( amount1 ) as amountOne + group > on year > > it will only return amountTwo. > > Anyone got an idea why this behaviour occurs and how to fix it? > > We would be most grateful because it's quite urgent for a school > project. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Multiple SUM in query
Since I can't seem to find the answer on google, nor in the docs, I hope someone here can help me. Whenever I try to do a query with more then 1 SUM-field in it, it will only return the first SUM-field, not the other ones in its results. So for instance : SUM( amount1 ) as amountOne, SUM( amount2 ) as amountTwo + group on year will only return amountOne as result. When I do : SUM( amount2 ) as amountTwo, SUM( amount1 ) as amountOne + group on year it will only return amountTwo. Anyone got an idea why this behaviour occurs and how to fix it? We would be most grateful because it's quite urgent for a school project. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
