I have a sql query that is not quit

[84vette]

I have a sql query that is not quite right, I was trying to have the col1 field count the number of distinct po_vendor_codes under a distinct Item_part_number within the po_month and po_year ---- any suggestions ..

SELECT PO_MONTH, PO_YEAR, COUNT(DISTINCT
PO_VENDOR_CODE) FROM
(SELECT MONTH(PO_DATE) AS PO_MONTH,
YEAR(PO_DATE) AS PO_YEAR, PO_VENDOR_CODE AS VENDOR,
PO_VENDOR_CODE, ITEM_PART_NUMBER AS ITEMPART
FROM MATLPDBA.PO A , MATLPDBA.ITEM B
WHERE PO_NUMBER = ITEM_PO_NUMBER
AND PO_DATE = ITEM_PO_DATE
AND (MONTH(PO_DATE) > 00 AND MONTH(PO_DATE) < 13
AND YEAR(PO_DATE) IN (1999,2000,2001,2002,2003))) AS TEMP1
GROUP BY PO_MONTH, PO_YEAR;
producing the following report:

PO PO
MONTH YEAR COL1
----------- ----------- -----------
1 1999 1172
1 2000 1028
1 2001 1066
1 2002 956
1 2003 905
2 1999 1156
2 2000 1022
2 2001 1040
2 2002 946
2 2003 909
3 1999 1142
3 2000 1021
3 2001 1078
3 2002 962
3 2003 909
4 1999 1113
4 2000 1001
4 2001 1072
4 2002 992
4 2003 891

 

[BillDHS]

Try unduplicating the derived table and skip the distinct. Where is the item code in the outer select? How can a month not be between 1 and 12?:

SELECT PO_MONTH, PO_YEAR,
itempart, COUNT(PRO_VENDOR_CODE) FROM
(SELECT MONTH(PO_DATE) AS PO_MONTH,
YEAR(PO_DATE) AS PO_YEAR, PO_VENDOR_CODE AS VENDOR,
PO_VENDOR_CODE, ITEM_PART_NUMBER AS ITEMPART
FROM MATLPDBA.PO A , MATLPDBA.ITEM B
WHERE PO_NUMBER = ITEM_PO_NUMBER
AND PO_DATE = ITEM_PO_DATE
AND (MONTH(PO_DATE) > 00 AND MONTH(PO_DATE) < 13
AND YEAR(PO_DATE) IN (1999,2000,2001,2002,2003))
group by 1,2,3,4,5
) AS TEMP1
GROUP BY PO_MONTH, PO_YEAR;

 

[84vette]

I ran the query you sugested and came up with an error message at the bottom, I use the month parm to some times select only month 03 thru 08
SQL QUERY MODIFIED LINE 1

SELECT PO_MONTH, PO_YEAR,
ITEMPART, COUNT(PRO_VENDOR_CODE) FROM
(SELECT MONTH(PO_DATE) AS PO_MONTH,
YEAR(PO_DATE) AS PO_YEAR, PO_VENDOR_CODE AS VENDOR,
PO_VENDOR_CODE, ITEM_PART_NUMBER AS ITEMPART
FROM MATLPDBA.PO A , MATLPDBA.ITEM B
WHERE PO_NUMBER = ITEM_PO_NUMBER
AND PO_DATE = ITEM_PO_DATE
AND (MONTH(PO_DATE) > 00 AND MONTH(PO_DATE) < 13
AND YEAR(PO_DATE) IN (1999,2000,2001,2002,2003))
GROUP BY 1,2,3,4,5,5
) AS TEMP1
GROUP BY PO_MONTH, PO_YEAR;




QUERY MESSAGES:
SQL error at or before 1 (line 11, position 10).
*** END ***

1=Help 2=Run 3=End 4=Print 5=Chart 6=Draw
7=Backward 8=Forward 9=Form 10=Insert 11=Delete 12=Report
The query did not run. See QUERY panel for error messages.
COMMAND ===>

 

[r937]

i don't think you can GROUP BY 1,2,3,4,5,5, and i don't think you need to do a GROUP BY in the derived table

also, in the derived table, you don't need to select both
PO_VENDOR_CODE AS VENDOR and also PO_VENDOR_CODE

and i agree, the test for MONTH between 1 and 12 is not necessary

if you want to count distinct vendors for a particular item number, item number has to go into both the SELECT and the GROUP BY in the outer query

[tt]SELECT PO_MONTH
, PO_YEAR
, ITEM_PART_NUMBER
, COUNT(DISTINCT PO_VENDOR_CODE)
FROM (
SELECT MONTH(PO_DATE) AS PO_MONTH
, YEAR(PO_DATE) AS PO_YEAR
, PO_VENDOR_CODE
, ITEM_PART_NUMBER
FROM MATLPDBA.PO A
, MATLPDBA.ITEM B
WHERE PO_NUMBER = ITEM_PO_NUMBER
AND PO_DATE = ITEM_PO_DATE
AND YEAR(PO_DATE)
IN (1999,2000,2001,2002,2003)
) AS TEMP1
GROUP
BY PO_MONTH
, PO_YEAR
, ITEM_PART_NUMBER [/tt]

rudy

http://r937.com/

6=Draw -- QMF, right?

 

[84vette]

I guess what I was teying to accomplish is not possible since I don't wand to see the details (item_number) but just the totals of the vendors like below:

PO PO
MONTH YEAR COL1
----------- ----------- -----------
1 1999 1172

 

[r937]

well, you did say &quot;count the number of distinct po_vendor_codes under a distinct Item_part_number&quot;

try the query again, but remove ITEM_PART_NUMBER from both the outer SELECT list and its GROUP BY

rudy