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

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

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

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
>

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
>

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

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

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
>