OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

x

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Robby
OOo Advocate
OOo Advocate


Joined: 21 Sep 2004
Posts: 249

PostPosted: Sun Sep 14, 2008 12:49 am    Post subject: x Reply with quote

xxx

Last edited by Robby on Sun Dec 11, 2011 1:56 am; edited 2 times in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Sep 14, 2008 9:25 am    Post subject: Reply with quote

Robby:

You might try something like this:

Code:
SELECT
   YEAR("Datum") as "Year",
   CASE WHEN "Aftrekcategorie" = 0 THEN SUM("Bedrag_giro") ELSE Null END as "0",
   CASE WHEN "Aftrekcategorie" = 1 THEN SUM("Bedrag_giro") ELSE Null END as "1"
   
FROM "Uitgaven" as "Uitgaven"

GROUP BY YEAR("Datum"), "Aftrekcategorie"


The above code . . . can ONLY be run directly to the HSQL database engine ( from the Menu: Edit -> Run SQL command directly << Must have a CHECK MARK ).

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Robby
OOo Advocate
OOo Advocate


Joined: 21 Sep 2004
Posts: 249

PostPosted: Sun Sep 14, 2008 10:53 am    Post subject: Reply with quote

xxx

Last edited by Robby on Sun Dec 11, 2011 1:57 am; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Sep 14, 2008 2:54 pm    Post subject: Reply with quote

Robby:

You might want to try code like this . . . NOTE . . . code below is using CASEWHEN ( one word ) . . . AND . . . the use of COALESCE will avoid any 'issues' you may have had with NULL values rather than 'defaulting to zero'.

Code:
SELECT
    YEAR("Datum") as "Jaar",
    SUM(CASEWHEN("Aftrekcategorie" = 0, COALESCE("Bedrag_giro", Null) + COALESCE("Bedrag_kas", Null), Null ) ) as "Advertenties",
    SUM(CASEWHEN("Aftrekcategorie" = 1, COALESCE("Bedrag_giro", Null) + COALESCE("Bedrag_kas", Null), Null ) ) as "Beroepskosten",
    SUM(CASEWHEN("Aftrekcategorie" = 2, COALESCE("Bedrag_giro", Null), Null ) ) as "Diversen",
    SUM(CASEWHEN("Aftrekcategorie" = 3, COALESCE("Bedrag_giro", Null), Null ) ) as "Huisvesting",
    SUM(CASEWHEN("Aftrekcategorie" = 4, COALESCE("Bedrag_giro", Null), Null ) ) as "Inventaris",
    SUM(CASEWHEN("Aftrekcategorie" = 5, COALESCE("Bedrag_giro", Null), Null ) ) as "Kantoorbenodigdheden",
    SUM(CASEWHEN("Aftrekcategorie" = 6, COALESCE("Bedrag_giro", Null), Null ) ) as "Literatuur",
    SUM(CASEWHEN("Aftrekcategorie" = 7, COALESCE("Bedrag_giro", Null), Null ) ) as "Opleidingskosten",
    SUM(CASEWHEN("Aftrekcategorie" = 8, COALESCE("Bedrag_giro", Null), Null ) ) as "Porto",
    SUM(CASEWHEN("Aftrekcategorie" = 9, COALESCE("Bedrag_giro", Null), Null ) ) as "Reiskosten"

FROM "Uitgaven" as "Uitgaven"

GROUP BY YEAR("Datum")

ORDER BY YEAR("Datum")


ALTERNATIVELY . . . you could code your Query this way, with the use of CASE WHEN . . . THEN . . . END:
Code:
SELECT
    YEAR("Datum") as "Jaar",
    SUM(CASE WHEN "Aftrekcategorie" = 0 THEN COALESCE("Bedrag_giro", Null) + COALESCE("Bedrag_kas", Null) END ) as "Advertenties",
    SUM(CASE WHEN "Aftrekcategorie" = 1 THEN COALESCE("Bedrag_giro", Null) + COALESCE("Bedrag_kas", Null) END ) as "Beroepskosten",
    SUM(CASE WHEN "Aftrekcategorie" = 2 THEN COALESCE("Bedrag_giro", Null) END ) as "Diversen",
    SUM(CASE WHEN "Aftrekcategorie" = 3 THEN COALESCE("Bedrag_giro", Null) END ) as "Huisvesting",
    SUM(CASE WHEN "Aftrekcategorie" = 4 THEN COALESCE("Bedrag_giro", Null) END ) as "Inventaris",
    SUM(CASE WHEN "Aftrekcategorie" = 5 THEN COALESCE("Bedrag_giro", Null) END ) as "Kantoorbenodigdheden",
    SUM(CASE WHEN "Aftrekcategorie" = 6 THEN COALESCE("Bedrag_giro", Null) END ) as "Literatuur",
    SUM(CASE WHEN "Aftrekcategorie" = 7 THEN COALESCE("Bedrag_giro", Null) END ) as "Opleidingskosten",
    SUM(CASE WHEN "Aftrekcategorie" = 8 THEN COALESCE("Bedrag_giro", Null) END ) as "Porto",
    SUM(CASE WHEN "Aftrekcategorie" = 9 THEN COALESCE("Bedrag_giro", Null) END ) as "Reiskosten"

FROM "Uitgaven" as "Uitgaven"

GROUP BY YEAR("Datum")

ORDER BY YEAR("Datum")


You might 'enjoy' looking at the link below, for a 'discussion' about "Cross-Tabulations" and how to code them with SQL.

http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html

I hope this helps, please be sure to let me / us know. Very Happy

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group