Page 1 of 1

Update/Select/GroupBy

Posted: Fri Sep 21, 2007 5:26 am
by BigJonMX
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


This is killing me, can anyone help [s]plz[/s] [size=150][color=green]please[/color][/size]...

This works as it should.
From the table bbprepay, i get monthly totals and put them in v8totals.
[syntax="sql"]insert ignore into v8totals (tid,tyy,tmm,finances)
select '3218',year(ppdate),month(ppdate),sum(ppamount) from bbprepay 
where fkidgente=3218 
group by year(ppdate),month(ppdate)
But if there already is a record, i need it updated ??????????????????????

Code: Select all

update v8totals (tid,tyy,tmm,finances)
select '3218',year(ppdate),month(ppdate),sum(ppamount) from bbprepay 
where fkidgente=3218 
group by year(ppdate),month(ppdate)
#fails!!!!!

Code: Select all

update v8totals,bbprepay
set finances=(select sum(ppamount) from bbprepay group by year(ppdate),month(ppdate))
where fkidgente=3218 and year(ppdate)=tyy and month(ppdate)=tmm
#fails!!!!!


[s]plz[/s] please, does anyone know how to do an Update...Select that uses GroupBy ???????????
PS. i can't delete records first, as there arwe other columns in v8totals.
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:11. Please use proper, complete spelling when posting in the forums. AOL Speak, leet speak and other abbreviated wording can confuse those that are trying to help you (or those that you are trying to help). Please keep in mind that there are many people from many countries that use our forums to read, post and learn. They do not always speak English as well as some of us, nor do they know these aberrant abbreviations. Therefore, use as few abbreviations as possible, especially when using such simple words.

Some examples of what not to do are ne1, any1 (anyone); u (you); ur (your or you're); 2 (to too); prolly (probably); afaik (as far as I know); etc.

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Fri Sep 21, 2007 5:59 am
by BigJonMX
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Sorry, i should have added PrimaryKey=(tid,tyy,tmm)

I think i got it...  
[syntax="sql"]INSERT IGNORE INTO v8totals (tid,tyy,tmm,finances)
SELECT '3218',YEAR(ppdate),MONTH(ppdate),SUM(ppamount) FROM bbprepay 
WHERE fkidgente='3218' GROUP BY YEAR(ppdate),MONTH(ppdate)
ON DUPLICATE KEY UPDATE finances=(SELECT SUM(ppamount) FROM bbprepay 
WHERE fkidgente='3218' AND tyy=YEAR(ppdate) AND tmm=MONTH(ppdate) GROUP BY YEAR(ppdate),MONTH(ppdate))
But i will have to check it lots as i'm not sure it will always do what i want
(and it looks slow and inefficient haveing the Select twice)
If anyone knows of another way please tell me.


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]