OpenOffice.org Forum

[Home] [FAQ] [Search] [Memberlist] [Usergroups] [Register]

[Profile] [Log in to check your private messages] [Log in]

OOoForum.org Forum Index -> OpenOffice.org Base

View previous topic :: View next topic

Author

Message

Robby
OOo Advocate

Joined: 21 Sep 2004
Posts: 249

Posted: Sun Sep 14, 2008 12:49 am Post subject: x

xxx Last edited by Robby on Sun Dec 11, 2011 1:56 am; edited 2 times in total

Sliderule
Super User

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

Posted: Sun Sep 14, 2008 9:25 am Post subject:

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.

Robby
OOo Advocate

Joined: 21 Sep 2004
Posts: 249

Posted: Sun Sep 14, 2008 10:53 am Post subject:

xxx Last edited by Robby on Sun Dec 11, 2011 1:57 am; edited 1 time in total

Sliderule
Super User

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

Posted: Sun Sep 14, 2008 2:54 pm Post subject:

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.

Display posts from previous:

	OOoForum.org Forum Index -> OpenOffice.org Base	All times are GMT - 8 Hours
Page 1 of 1

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