Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 9 of 9 total |
Sum to different columns depending upon year |
Sun, Sep 2 2007 9:48 AM | Permanent Link |
"Robert Rowlands" | Hello.
I have the following query. SELECT SUM(InvNet) AS Net, Extract(Year FROM InvoiceDate) As Year, Extract(Month From InvoiceDate) AS Month FROM ARInv GROUP BY Year, Month This gives me 3 columns. I want to use the data in a QG Chart and in order for the user to be able to show the results from different years side by side I need the value NET to comprise a number of columns depending upon the Year of InvoiceDate. So, I need the second line to do a SUM to a column named after the Year value of InvoiceDate. The number of columns would be the same as the number of different years in InvoiceDate. I still want the Grouping to be by Month. (The Year is not important if it is possible) Output would be something like this Month 2002 2003 2004 2005 etc 1 £XXX £XXX £XXX 2 £XXX £XXX £XXX 3 4 5 6 7 8 9 10 11 12 How can I achieve this? Thanks. Rob. |
Sun, Sep 2 2007 1:48 PM | Permanent Link |
"Robert" | "Robert Rowlands" <rob@notathome.com> wrote in message news:29FABDA5-C185-4A09-BECC-0AFE2CC125C4@news.elevatesoft.com... > Hello. > > I have the following query. SELECT SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2002 THEN INVNET ELSE 0)) AS TOT2002, SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2003 THEN INVNET ELSE 0)) AS TOT2003, EXTRACT (MONTH FORM INVOICEDATE) AS MONTH GROUP BY 3; You should be able to use parameters instead of hardcoded year numbers. Robert > > SELECT > SUM(InvNet) AS Net, > Extract(Year FROM InvoiceDate) As Year, > Extract(Month From InvoiceDate) AS Month > FROM > ARInv > GROUP BY Year, Month > > This gives me 3 columns. > > I want to use the data in a QG Chart and in order for the user to be able > to show the results from different years side by side I need the value NET > to comprise a number of columns depending upon the Year of InvoiceDate. > > So, I need the second line to do a SUM to a column named after the Year > value of InvoiceDate. The number of columns would be the same as the > number of different years in InvoiceDate. I still want the Grouping to be > by Month. (The Year is not important if it is possible) > > Output would be something like this > > Month 2002 2003 2004 2005 > etc > 1 £XXX £XXX £XXX > 2 £XXX £XXX £XXX > 3 > 4 > 5 > 6 > 7 > 8 > 9 > 10 > 11 > 12 > > How can I achieve this? > > Thanks. > > Rob. > |
Sun, Sep 2 2007 3:04 PM | Permanent Link |
"Robert Rowlands" | Thanks Robert
I need to spend some time and learn SQL properly. Now, I'm really pushing my luck here, but is there any way I can check within the SQL what years are available in the InvoiceDate Field ands then apply your code? To be fair, I guess I could check what dates are available in the table and then create the SQL in the form's OnCreate event. Rob. "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message news:92072FA6-ABEE-47D6-80F6-A0034A72E2F9@news.elevatesoft.com... > > "Robert Rowlands" <rob@notathome.com> wrote in message > news:29FABDA5-C185-4A09-BECC-0AFE2CC125C4@news.elevatesoft.com... >> Hello. >> >> I have the following query. > > SELECT SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2002 THEN INVNET ELSE 0)) > AS TOT2002, > SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2003 THEN INVNET ELSE 0)) AS > TOT2003, > EXTRACT (MONTH FORM INVOICEDATE) AS MONTH > GROUP BY 3; > > You should be able to use parameters instead of hardcoded year numbers. > > Robert > >> >> SELECT >> SUM(InvNet) AS Net, >> Extract(Year FROM InvoiceDate) As Year, >> Extract(Month From InvoiceDate) AS Month >> FROM >> ARInv >> GROUP BY Year, Month >> >> This gives me 3 columns. >> >> I want to use the data in a QG Chart and in order for the user to be able >> to show the results from different years side by side I need the value >> NET to comprise a number of columns depending upon the Year of >> InvoiceDate. >> >> So, I need the second line to do a SUM to a column named after the Year >> value of InvoiceDate. The number of columns would be the same as the >> number of different years in InvoiceDate. I still want the Grouping to >> be by Month. (The Year is not important if it is possible) >> >> Output would be something like this >> >> Month 2002 2003 2004 2005 etc >> 1 £XXX £XXX £XXX >> 2 £XXX £XXX £XXX >> 3 >> 4 >> 5 >> 6 >> 7 >> 8 >> 9 >> 10 >> 11 >> 12 >> >> How can I achieve this? >> >> Thanks. >> >> Rob. >> > > |
Tue, Sep 4 2007 9:33 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Robert,
<< You should be able to use parameters instead of hardcoded year numbers. >> Unfortunately you cannot use parameters in SELECT expressions. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Sep 4 2007 9:34 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Robert,
<< To be fair, I guess I could check what dates are available in the table and then create the SQL in the form's OnCreate event. >> That's really the only way to do it dynamically since the content of the SELECT expressions is dependent upon knowing the desired years in advance. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Sep 4 2007 9:45 AM | Permanent Link |
"Robert" | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:3A075548-587E-4732-BE9A-45EBDE8CD144@news.elevatesoft.com... > Robert, > > << You should be able to use parameters instead of hardcoded year numbers. > >> > > Unfortunately you cannot use parameters in SELECT expressions. > I never remember exactly what the rules are here. Obviously, you can do a SELECT ... WHERE FIELD = :Param, so it is not clear why parameters would not work with other flavors of SELECT. One of them things, I guess. Robert |
Wed, Sep 5 2007 2:44 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Robert,
<< I never remember exactly what the rules are here. Obviously, you can do a SELECT ... WHERE FIELD = :Param, so it is not clear why parameters would not work with other flavors of SELECT. One of them things, I guess. >> The issue really comes down to one of allowing the database engine to finalize what the result set will look like after a query is compiled/prepared. This is important for a lot of reasons, and using dynamic parameters in the SELECT expressions list would prevent the database engine from being able to determine 100% what each SELECT expression is in terms of type, size, etc. This isn't the case with WHERE or JOIN clauses, so they are permitted to contain dynamic parameters. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Sep 5 2007 3:08 PM | Permanent Link |
"Robert" | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:5A38D855-C076-4DC0-8042-73D10B586D82@news.elevatesoft.com... > Robert, > > << I never remember exactly what the rules are here. Obviously, you can do > a SELECT ... WHERE FIELD = :Param, so it is not clear why parameters would > not work with other flavors of SELECT. One of them things, I guess. >> > > The issue really comes down to one of allowing the database engine to > finalize what the result set will look like after a query is > compiled/prepared. This is important for a lot of reasons, and using > dynamic parameters in the SELECT expressions list would prevent the > database engine from being able to determine 100% what each SELECT > expression is in terms of type, size, etc. > > This isn't the case with WHERE or JOIN clauses, so they are permitted to > contain dynamic parameters. > Uh? This was the original statement in question SELECT SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = 2002 THEN INVNET ELSE 0)) AS TOT2002, Why should this cause any problem? SELECT SUM(IF (EXTRACT(YEAR FROM INVOICEDATE) = :y1 THEN INVNET ELSE 0)) AS TOT2002, Robert |
Fri, Sep 7 2007 6:04 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Robert,
<< Why should this cause any problem? >> It wouldn't. However, the possibilities with parameters in the SELECT expression list is not just limited to one particular SQL statement. Someone could just as easily try this: SELECT :MyParam FROM MyTable The question in this case is, what type/size is :MyParam at query compilation/prepare time ? -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |