Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Coalasce/sum issue |
Mon, May 1 2017 7:58 AM | Permanent Link |
Huseyin Aliz myBiss ApS | Hi All,
I have following sql: select field1,coalesce(sum(field2),0) as sumfield2,coalesce(sum(field3),0) as sumfield3 from table where field=1 and dato between '2017-05-01' and '2017-05-01' group by field1 order by field1 If no records are found with the query nothing (no sums) are shown - I want sumfield2 and sumfield3 to show '0' even if there are no records matching dato date between. If i change coalasce(sum(field2,0) to sum(coalasce(field2,0) nothing changes. I guess i am misunderstanding something, can anyone spot what's wrong? Latest DBISAM. Regards, Hüseyin |
Tue, May 2 2017 4:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Hüseyin
To summarise what you want - the query should return something even when there's nothing to return - not sure this is sensible / possible select field1,coalesce(sum(field2),0) as sumfield2,coalesce(sum(field3),0) as sumfield3 from table where field=1 and dato between '2017-05-01' and '2017-05-01' group by field1 order by field1 <<<<<<<<<<< not sure that this is needed You could try a UNION statement eg UNION SELECT 0,0,0 FROM table but that will give you 0,0,0 always, even when there's data. You should be able to do something with a script - I'm working on something right now but when that's done, if no one else contributes, I'll have a look (probably tomorrow) Roy Lambert |
Tue, May 2 2017 1:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Hüseyin,
<< If no records are found with the query nothing (no sums) are shown - I want sumfield2 and sumfield3 to show '0' even if there are no records matching dato date between. >> What do you want to show for the field1 in the "default" result set ? Tim Young Elevate Software www.elevatesoft.com |
Wed, May 3 2017 3:34 AM | Permanent Link |
Huseyin Aliz myBiss ApS | Hi Roy,
Thanks for looking at it. Regards, Hüseyin Den 02/05/2017 kl. 10.23 skrev Roy Lambert: > Hüseyin > > > To summarise what you want - the query should return something even when there's nothing to return - not sure this is sensible / possible > > select field1,coalesce(sum(field2),0) as > sumfield2,coalesce(sum(field3),0) as sumfield3 > from table > where field=1 > and dato between '2017-05-01' and '2017-05-01' > group by field1 > order by field1 <<<<<<<<<<< not sure that this is needed > > You could try a UNION statement eg > > UNION > SELECT > 0,0,0 > FROM table > > but that will give you 0,0,0 always, even when there's data. You should be able to do something with a script - I'm working on something right now but when that's done, if no one else contributes, I'll have a look (probably tomorrow) > > Roy Lambert > |
Wed, May 3 2017 3:36 AM | Permanent Link |
Huseyin Aliz myBiss ApS | Hi Tim,
Field1 is a parameter (actually customernumber), so it's value are known and set by me Regards, Hüseyin Den 02/05/2017 kl. 19.58 skrev Tim Young [Elevate Software]: > Hüseyin, > > << If no records are found with the query nothing (no sums) are shown - I want sumfield2 and sumfield3 to show '0' even if there are no records matching dato date between. >> > > What do you want to show for the field1 in the "default" result set ? > > Tim Young > Elevate Software > www.elevatesoft.com > |
Wed, May 3 2017 5:33 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Hüseyin
There may be something in the newer version of DBISAM that allows you to do what you want but I can't come up with something in my older (4.25b5) version. I can easily get the 0,0 row in there every time but can't just do it when there's no result. Hopefully Tim will know a technique. Roy Lambert |
Wed, May 3 2017 8:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Hüseyin
Sused it - a three stage script - forget grouping & summing until you have all the data gathered together. SELECT _BoxNo AS S0, _Size AS S1, _Status AS S2 INTO Memory\tmp1 FROM MandN WHERE _BoxNo = -1 AND _Received BETWEEN '1999-01-01' AND '2000-01-01'; INSERT INTO Memory\tmp1 VALUES(-1,0,0); SELECT S0, SUM(S1), SUM(S2) FROM Memory\tmp1 GROUP BY S0 ORDER BY S0; NB This will not work if you do a average rather than a sum. Not quite using your fields & structure but you should be able to use the concept Roy Lambert |
Thu, May 4 2017 4:49 AM | Permanent Link |
Huseyin Aliz myBiss ApS | Roy,
Great, thanks Den 03/05/2017 kl. 14.09 skrev Roy Lambert: > Hüseyin > > Sused it - a three stage script - forget grouping & summing until you have all the data gathered together. > > > SELECT > _BoxNo AS S0, > _Size AS S1, > _Status AS S2 > INTO Memory\tmp1 > FROM MandN > WHERE > _BoxNo = -1 > AND > _Received BETWEEN '1999-01-01' AND '2000-01-01'; > > INSERT INTO Memory\tmp1 VALUES(-1,0,0); > > > SELECT > S0, > SUM(S1), > SUM(S2) > FROM Memory\tmp1 > GROUP BY S0 > ORDER BY S0; > > NB This will not work if you do a average rather than a sum. Not quite using your fields & structure but you should be able to use the concept > > > Roy Lambert > |
This web page was last updated on Tuesday, April 23, 2024 at 08:10 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |