|
Home > Archive > Visual FoxPro SQL Queries > March 2006 > missing data/records in group by
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
missing data/records in group by
|
|
|
| urgent help needed....
I am using VFP9, enginebehavior 70 with this query:
set unique off
SELECT billarchive.name, ;
ALLTRIM(STR(MONTH(mo
nth_end)))+"/" +ALLTRIM(STR(YEAR(mo
nth_end))) as period, ;
SUM(billarchive.stand_hrs), ;
ROUND(SUM(billarchiv
e.stand_hrs)*P_level.urs_rate,2) as "RawTotal", ;
ROUND(SUM(billarchiv
e.stand_hrs)*p_level.rate,2) as " billarchiveableTotal
", ;
ROUND(SUM(billarchiv
e.stand_hrs)*p_level.rate,2)
- ROUND(SUM(billarchiv
e.stand_hrs)*P_level.urs_rate,2) as "NetIncome" ;
FROM billarchive, p_level;
WHERE billarchive.vendor = p_level.vendor;
GROUP BY 2, name
It does not return the total of all my records. I've had this problem with
another similar query, it seems to ignore records that are identical --i.e.,
name and amt field are the same, so it only adds the 1st record.
there is no order set on the table.
what am I doing wrong?
THANKS!
MK
| |
| Cindy Winegarden 2006-03-23, 8:42 pm |
| Hi MK,
"...name and amt field are the same..."
I don't see an Amt field in your query. Is it a column that isn't in the
query, or are you referring to Stand_Hrs?
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
"MK" <MK@discussions.microsoft.com> wrote in message
news:2CF6CBF4-C878-4811-BF9A- 61016126E371@microso
ft.com...
> urgent help needed....
>
> I am using VFP9, enginebehavior 70 with this query:
>
> set unique off
>
> SELECT billarchive.name, ;
> ALLTRIM(STR(MONTH(mo
nth_end)))+"/" +ALLTRIM(STR(YEAR(mo
nth_end))) as
> period, ;
> SUM(billarchive.stand_hrs), ;
> ROUND(SUM(billarchiv
e.stand_hrs)*P_level.urs_rate,2) as "RawTotal", ;
> ROUND(SUM(billarchiv
e.stand_hrs)*p_level.rate,2) as
> " billarchiveableTotal
", ;
> ROUND(SUM(billarchiv
e.stand_hrs)*p_level.rate,2)
> - ROUND(SUM(billarchiv
e.stand_hrs)*P_level.urs_rate,2) as "NetIncome" ;
> FROM billarchive, p_level;
> WHERE billarchive.vendor = p_level.vendor;
> GROUP BY 2, name
>
> It does not return the total of all my records. I've had this problem
> with
> another similar query, it seems to ignore records that are
> identical --i.e.,
> name and amt field are the same, so it only adds the 1st record.
>
> there is no order set on the table.
>
> what am I doing wrong?
>
> THANKS!
> MK
|
|
|
|
|