SQL
 

I written a query where i pull debtor number and and amont of sales from a
period of specified time and all works well.
It's is returning a line for every debtor transaction where i want the total
for all transaction over the year.
So 1 line for the debtor with the total transaction for the year.

SELECT dih_dbtno, dbt_name, dih_type, dih_date, dih_eom ,sum(dil_ordertotal)
from xxxxxx_xxxxxx_xxxxxxx_,xxxxxx_xxxxxxx_xxxx_xxxx,xx xxxx_xxxxxx_xxxx
where dih_link = dil_dihlink
and dih_dbtno = dbt_no
And dih_eom '14'
and dih_eom < '27'
and dih_type= '73'
Group By dih_dbtno , dbt_name, dih_type, dih_date, dih_eom

SQL
 

Guessing here because I don't know what your fields stand for, but it looks
like dih_eom might be (one of) the culprits. eom is end-of-month right? So
if you want yearly data, but are selecting the more-granular monthly data, a
year aggregation is not possible. I would look at stuff like that. Any
transaction-level data that is unique, represents less than a yearly
timeframe and can't be summed should be omitted from the select.


"Murray" wrote in message
...
I written a query where i pull debtor number and and amont of sales from a
period of specified time and all works well.
It's is returning a line for every debtor transaction where i want the
total
for all transaction over the year.
So 1 line for the debtor with the total transaction for the year.

SELECT dih_dbtno, dbt_name, dih_type, dih_date, dih_eom
,sum(dil_ordertotal)
from xxxxxx_xxxxxx_xxxxxxx_,xxxxxx_xxxxxxx_xxxx_xxxx,xx xxxx_xxxxxx_xxxx
where dih_link = dil_dihlink
and dih_dbtno = dbt_no
And dih_eom '14'
and dih_eom < '27'
and dih_type= '73'
Group By dih_dbtno , dbt_name, dih_type, dih_date, dih_eom

SQL
 

Thanks that did the trick , removed eom and date fields and workd perfectly

"Tim Zych" wrote:

Guessing here because I don't know what your fields stand for, but it looks
like dih_eom might be (one of) the culprits. eom is end-of-month right? So
if you want yearly data, but are selecting the more-granular monthly data, a
year aggregation is not possible. I would look at stuff like that. Any
transaction-level data that is unique, represents less than a yearly
timeframe and can't be summed should be omitted from the select.


"Murray" wrote in message
...
I written a query where i pull debtor number and and amont of sales from a
period of specified time and all works well.
It's is returning a line for every debtor transaction where i want the
total
for all transaction over the year.
So 1 line for the debtor with the total transaction for the year.

SELECT dih_dbtno, dbt_name, dih_type, dih_date, dih_eom
,sum(dil_ordertotal)
from xxxxxx_xxxxxx_xxxxxxx_,xxxxxx_xxxxxxx_xxxx_xxxx,xx xxxx_xxxxxx_xxxx
where dih_link = dil_dihlink
and dih_dbtno = dbt_no
And dih_eom '14'
and dih_eom < '27'
and dih_type= '73'
Group By dih_dbtno , dbt_name, dih_type, dih_date, dih_eom