Distinct records on old school foxpro database file
-
I've been messing with this for hours today. I'm trying to do this in 1 call to the database. Normally I would just get a list of unique items first, and then go back and get the numbers. Perhaps I'm trying to do something that can't be done, or it's just beyond my knowledge level, I'll take the latter of thought. I need to get the qty sum and records of a sales history file Item | qty | custNum | date 09-170 | 5 | 0014 | 01/20/1990 09-170 | 24 | 0014 | 02/21/1991 17-209 | 24 | 0014 | 03/15/1997 17-209 | 24 | 0014 | 08/15/2007 Results should be Item | qty | desc | avg price 09-170 | 29 | trowel | 3.00 17-209 | 48 | kneepad | 24.00 So I wrote this to get the count of unique items which is 2. I'm not sure if my code returns the right number or not, because the database file contains thousands of records. I get 14, not reflective of the example above.
SELECT COUNT(FITEMNO) AS cCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)
UNION ALL
SELECT COUNT(FITEMNO) AS hCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)I wrote this to get the records, in which I get 36. I'm just testing on the history file, so there is no union to join the 2 database files yet. I keep getting 36 records with duplicates instead of 14 unique records.
SELECT FITEMNO, FSHIPQTY AS hItems"
FROM
(
SELECT h.FITEMNO, h.FSHIPQTY FROM ARTRS01H.dbf h WHERE h.FCUSTNO=@FCUSTNO GROUP BY FITEMNO, FSHIPQTY
) -
I've been messing with this for hours today. I'm trying to do this in 1 call to the database. Normally I would just get a list of unique items first, and then go back and get the numbers. Perhaps I'm trying to do something that can't be done, or it's just beyond my knowledge level, I'll take the latter of thought. I need to get the qty sum and records of a sales history file Item | qty | custNum | date 09-170 | 5 | 0014 | 01/20/1990 09-170 | 24 | 0014 | 02/21/1991 17-209 | 24 | 0014 | 03/15/1997 17-209 | 24 | 0014 | 08/15/2007 Results should be Item | qty | desc | avg price 09-170 | 29 | trowel | 3.00 17-209 | 48 | kneepad | 24.00 So I wrote this to get the count of unique items which is 2. I'm not sure if my code returns the right number or not, because the database file contains thousands of records. I get 14, not reflective of the example above.
SELECT COUNT(FITEMNO) AS cCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)
UNION ALL
SELECT COUNT(FITEMNO) AS hCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)I wrote this to get the records, in which I get 36. I'm just testing on the history file, so there is no union to join the 2 database files yet. I keep getting 36 records with duplicates instead of 14 unique records.
SELECT FITEMNO, FSHIPQTY AS hItems"
FROM
(
SELECT h.FITEMNO, h.FSHIPQTY FROM ARTRS01H.dbf h WHERE h.FCUSTNO=@FCUSTNO GROUP BY FITEMNO, FSHIPQTY
)If you want the distinct count of FITEMO use:
SELECT COUNT(distinct FITEMNO)
FROM ARTRS01.dbf
WHERE FCUSTNO=@FCUSTNO“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I've been messing with this for hours today. I'm trying to do this in 1 call to the database. Normally I would just get a list of unique items first, and then go back and get the numbers. Perhaps I'm trying to do something that can't be done, or it's just beyond my knowledge level, I'll take the latter of thought. I need to get the qty sum and records of a sales history file Item | qty | custNum | date 09-170 | 5 | 0014 | 01/20/1990 09-170 | 24 | 0014 | 02/21/1991 17-209 | 24 | 0014 | 03/15/1997 17-209 | 24 | 0014 | 08/15/2007 Results should be Item | qty | desc | avg price 09-170 | 29 | trowel | 3.00 17-209 | 48 | kneepad | 24.00 So I wrote this to get the count of unique items which is 2. I'm not sure if my code returns the right number or not, because the database file contains thousands of records. I get 14, not reflective of the example above.
SELECT COUNT(FITEMNO) AS cCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)
UNION ALL
SELECT COUNT(FITEMNO) AS hCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)I wrote this to get the records, in which I get 36. I'm just testing on the history file, so there is no union to join the 2 database files yet. I keep getting 36 records with duplicates instead of 14 unique records.
SELECT FITEMNO, FSHIPQTY AS hItems"
FROM
(
SELECT h.FITEMNO, h.FSHIPQTY FROM ARTRS01H.dbf h WHERE h.FCUSTNO=@FCUSTNO GROUP BY FITEMNO, FSHIPQTY
)I must be missing something here. Why can't you just:
SELECT item,sum(qty) as qty
FROM MyTable
GROUP BY itemWrong is evil and must be defeated. - Jeff Ello[^]
-
If you want the distinct count of FITEMO use:
SELECT COUNT(distinct FITEMNO)
FROM ARTRS01.dbf
WHERE FCUSTNO=@FCUSTNO“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I must be missing something here. Why can't you just:
SELECT item,sum(qty) as qty
FROM MyTable
GROUP BY itemWrong is evil and must be defeated. - Jeff Ello[^]
I ended up doing something similar. I wrote one function to get the distinct items, and went back and got the sums with the distinct item list. I don't know what I was thinking, was trying to do it all in one shot.
" SELECT " & _
" SUM(FSHIPQTY) " & _
", SUM(FAMOUNT) " & _
" FROM ARTRS01H.dbf " & _
" WHERE FCUSTNO=@FCUSTNO " & _
" AND FITEMNO=@FITEMNO " -
I've been messing with this for hours today. I'm trying to do this in 1 call to the database. Normally I would just get a list of unique items first, and then go back and get the numbers. Perhaps I'm trying to do something that can't be done, or it's just beyond my knowledge level, I'll take the latter of thought. I need to get the qty sum and records of a sales history file Item | qty | custNum | date 09-170 | 5 | 0014 | 01/20/1990 09-170 | 24 | 0014 | 02/21/1991 17-209 | 24 | 0014 | 03/15/1997 17-209 | 24 | 0014 | 08/15/2007 Results should be Item | qty | desc | avg price 09-170 | 29 | trowel | 3.00 17-209 | 48 | kneepad | 24.00 So I wrote this to get the count of unique items which is 2. I'm not sure if my code returns the right number or not, because the database file contains thousands of records. I get 14, not reflective of the example above.
SELECT COUNT(FITEMNO) AS cCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)
UNION ALL
SELECT COUNT(FITEMNO) AS hCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)I wrote this to get the records, in which I get 36. I'm just testing on the history file, so there is no union to join the 2 database files yet. I keep getting 36 records with duplicates instead of 14 unique records.
SELECT FITEMNO, FSHIPQTY AS hItems"
FROM
(
SELECT h.FITEMNO, h.FSHIPQTY FROM ARTRS01H.dbf h WHERE h.FCUSTNO=@FCUSTNO GROUP BY FITEMNO, FSHIPQTY
)SELECT h.FITEMNO, SUM(h.FSHIPQTY) AS TOTAL_QTY FROM ARTRS01H.dbf h WHERE h.FCUSTNO=@FCUSTNO GROUP BY FITEMNO
-
I've been messing with this for hours today. I'm trying to do this in 1 call to the database. Normally I would just get a list of unique items first, and then go back and get the numbers. Perhaps I'm trying to do something that can't be done, or it's just beyond my knowledge level, I'll take the latter of thought. I need to get the qty sum and records of a sales history file Item | qty | custNum | date 09-170 | 5 | 0014 | 01/20/1990 09-170 | 24 | 0014 | 02/21/1991 17-209 | 24 | 0014 | 03/15/1997 17-209 | 24 | 0014 | 08/15/2007 Results should be Item | qty | desc | avg price 09-170 | 29 | trowel | 3.00 17-209 | 48 | kneepad | 24.00 So I wrote this to get the count of unique items which is 2. I'm not sure if my code returns the right number or not, because the database file contains thousands of records. I get 14, not reflective of the example above.
SELECT COUNT(FITEMNO) AS cCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)
UNION ALL
SELECT COUNT(FITEMNO) AS hCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE FCUSTNO=@FCUSTNO
GROUP BY FITEMNO
)I wrote this to get the records, in which I get 36. I'm just testing on the history file, so there is no union to join the 2 database files yet. I keep getting 36 records with duplicates instead of 14 unique records.
SELECT FITEMNO, FSHIPQTY AS hItems"
FROM
(
SELECT h.FITEMNO, h.FSHIPQTY FROM ARTRS01H.dbf h WHERE h.FCUSTNO=@FCUSTNO GROUP BY FITEMNO, FSHIPQTY
)I finally got it in 1 shot. Runs super fast now. Customer complained about the 5 minute run time, so I took another stab at it. Don't know why it I got it this time, perhaps the nap time and the beers!
SELECT
DISTINCT v.FITEMNO
, SUM(v.FSHIPQTY)
, SUM(v.FSHIPQTY * v.FPRICE)
, (SELECT FDESCRIPT FROM ICITM01.dbf WHERE FITEMNO=v.FITEMNO) AS FREALDESC
FROM ARTRS01H.dbf v
WHERE FCUSTNO=@FCUSTNO
GROUP BY v.FITEMNO