Select count on 2 DBF files
-
Sort of a dumb question here, I have 2 DBF files, fox-pro DBF files that are identical, in which I want to union all and get the count of the FITEMNO, which there is 1 in the first file and 1 in the 2nd file, so I get a count of 2. Is this normal behavior or did I construct the statement wrong below?
SELECT COUNT(FITEMNO) AS hCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE
FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
AND
FCUSTNO = @FCUSTNO
AND
FITEMNO = @FITEMNO
GROUP BY FITEMNO
UNION ALL
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE
FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
AND
FCUSTNO = @FCUSTNO
AND
FITEMNO = @FITEMNO
GROUP BY FITEMNO
) -
Sort of a dumb question here, I have 2 DBF files, fox-pro DBF files that are identical, in which I want to union all and get the count of the FITEMNO, which there is 1 in the first file and 1 in the 2nd file, so I get a count of 2. Is this normal behavior or did I construct the statement wrong below?
SELECT COUNT(FITEMNO) AS hCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE
FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
AND
FCUSTNO = @FCUSTNO
AND
FITEMNO = @FITEMNO
GROUP BY FITEMNO
UNION ALL
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE
FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
AND
FCUSTNO = @FCUSTNO
AND
FITEMNO = @FITEMNO
GROUP BY FITEMNO
)That's what you asked for. Suggestions: 0) Move the GROUP BY to the outer SELECT or 1) Use SUM in the outer SELECT
-
That's what you asked for. Suggestions: 0) Move the GROUP BY to the outer SELECT or 1) Use SUM in the outer SELECT