Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|