SQL query help

1185 pts.
Tags:
SQL
SQL queries
SQL Query
Below is a list of the query that I am working on. There are part numbers that may be match to more than one module number. What I need is to show only the part numbers for the list. These part numbers I need a list of only one part number for each group.

PartNo------ Module------ Numbers

79000123-- QWST123-------1

79000123-- ORTY123------- 1

79000023--- RTY0023-------1

I need a result that would look like

PartNo---------- Module------ Numbers

79000123---- QWST123-------2------ (it is just counting the pn column) 79000023-----RTY0023--------1

Could you give me a general statement that would just count the part numbers in the PartNo column and show the number of duplications, disregarding the Module numbers?





Software/Hardware used:
MSXP, Windows 2000 Acess 2000

Answer Wiki

Thanks. We'll let you know when a new response is added.

Roughly this:

Select partNo, max(Module), count(PartNbr) as “Numbers”
from Myfile
Group by partNo

Phil

Discuss This Question: 16  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CompEng
    This is where I added the max and the count. When I ran it, it give me the error [IBM][System i Access ODBC Drive][DB2 for i5/os]SQL0140-Token MAX was not valid. Valid tokens: , FROM INTO (#104) How would this be set up so that there would be no duplication in the Part Number (INMSP300.M3PRD as "PN") field. I only need one for any group or set of "OE nubmers or Part Numbers", Like mentioned above the part number may have multiple "OENo" numbers to one part number. I need a list of all the part numbers as to what is associated to them, if could be any one of the OE numbers in the list.. I need a count of all the different part numbers regardless of the OE numbers Select INMSP300.M3OE# as "OENo", INMSP300.M3OEC as "ComprsOE", INMSP300.M3PRD as "PN", INMSP300.M3CLS as "PrdLine", INMSP100.MSAPP as "Application", INMSP100.MSPRD as "PartNo", INMSP100.MSRDTM as "rtd-Month", INMSP100.MSRDTD as "rdc-Day", INMSP100.MSRDTY as "rtc-Year", INMSP100.MSMPC as "Phase" Max(INMSP300.M3OE# as "OENo"), Count(INMSP300.M3PRD as "PN") as numbers From rdb.CARF1.INMSP300 INMSP300 join rdb. CARF1.INMSP100 INMSP100 on M3PRD = MSPRD Where INMSP300.M3CLS ='78' and INMSP100.MSRDTY = '09' and INMSP100.MSRDTM = '11' order by INMSP100.MSMPC Group by INMSP300.M3PRD as "PN"
    1,185 pointsBadges:
    report
  • CompEng
    I have been doing some research on this and it seem like I might need a "Have" ralther then a "Where" is that so?
    1,185 pointsBadges:
    report
  • CompEng
    Would I need to nest a select statement or not?
    1,185 pointsBadges:
    report
  • CompEng
    Is there ever a time that you can use both the a "Have" and a "Where" in the same query. If so, is this the time that something like that would need to be used. The reason I ask that is due to the filter like "Where" statements that I are used at the end of my query statement?
    1,185 pointsBadges:
    report
  • carlosdl
    You are getting that error because a comma (,) is missing before the MAX keyword. As for your last questions, it is HAVING, not HAVE, and it is used when you want to filter the results based on the aggregate functions (i.e. sum, max, etc...). And yes, you can have both, 'where' and 'having' in the same command.
    69,365 pointsBadges:
    report
  • carlosdl
    Also, you will get errors if you don't GROUP BY all the columns you include in your select list. Why did you need to modify the query provided by Phil ? Is there some different requirment from the one you posted in your original question ? could you please provide more details about that ?
    69,365 pointsBadges:
    report
  • philpl1jb
    We were making a summary query .. necessary to get the count. In that type of query every column needs to be a summary function (max, min, count, etc) or a group by field. And you were missing a comma. Select max(INMSP300.M3OE#) as “OENo”, max(INMSP300.M3OEC) as “ComprsOE”, max(INMSP300.M3PRD) as “PN”, max(INMSP300.M3CLS) as “PrdLine”, max(INMSP100.MSAPP) as “Application”, max(INMSP100.MSPRD) as “PartNo”, max(INMSP100.MSRDTM) as “rtd-Month”, max(INMSP100.MSRDTD) as “rdc-Day”, max(INMSP100.MSRDTY) as “rtc-Year”, max(INMSP100.MSMPC) as “Phase”, INMSP300.M3OE# as “OENo”, Count(INMSP300.M3PRD ) as numbers From rdb.CARF1.INMSP300 INMSP300 join rdb. CARF1.INMSP100 INMSP100 on M3PRD = MSPRD Where INMSP300.M3CLS =’78′ and INMSP100.MSRDTY = ‘09′ and INMSP100.MSRDTM = ‘11′ order by INMSP100.MSMPC Group by INMSP300.M3PRD
    49,940 pointsBadges:
    report
  • philpl1jb
    The Where clause is used to determine which records are used from the two files A having clause would be used against a summary column ie having count(*) > 4 Hope this gets you near your answer I think a number of the columns maybe unnessary in the select. Phil
    49,940 pointsBadges:
    report
  • CompEng
    The initial statement that I posted was a genernic and the specific was what I recently posted. The reason I posted the generic was for me to give an example I did have enough space to show the specifics data. So I made up a general. Then I tried to apply the general statement to specific query and then I got the error that I just posted. Now I pasted what Phil posted and now I am getting an error [IBM]Sytem i Access ODBC Driver][DB2for i5/OS]SQL0122 - -Column MSMPC or expression oin the SELECT list not vaild. I reviewed all the feilds and as for as I can see they are OE Here is the statement Select max (INMSP300.M3OE#) as "OENo", max (INMSP300.M3OEC) as "ComprsOE", max (INMSP300.M3PRD) as "PN", max (INMSP300.M3CLS) as "PrdLine", max (INMSP100.MSAPP) as "Application", max (INMSP100.MSPRD) as "PartNo", max (INMSP100.MSRDTM) as "rtd-Month", max (INMSP100.MSRDTD) as "rdc-Day", max (INMSP100.MSRDTY) as "rtc-Year", max (INMSP100.MSMPC) as "Phase", Count (INMSP300.M3PRD) as "numbers" From rdb.CARF1.INMSP300 INMSP300 join rdb. CARF1.INMSP100 INMSP100 on M3PRD = MSPRD Where INMSP300.M3CLS ='79' and INMSP100.MSRDTY = '09' and INMSP100.MSRDTM = '11' order by INMSP100.MSMPC
    1,185 pointsBadges:
    report
  • CompEng
    So what is wronge with the above statement? if the files and fields are fine what eles could be the problem?
    1,185 pointsBadges:
    report
  • CompEng
    The OE in the last sentance of the posting of the query statement should be OK.. "I reviewed all the feilds and as for as I can see they are OE " should be OK not OE
    1,185 pointsBadges:
    report
  • philpl1jb
    Select INMSP300.M3PRD as “PN”, Count(INMSP300.M3PRD ) as numbers From rdb.CARF1.INMSP300 INMSP300 join rdb.CARF1.INMSP100 INMSP100 on M3PRD = MSPRD Where INMSP300.M3CLS =’78′ and INMSP100.MSRDTY = ‘09′ and INMSP100.MSRDTM = ‘11′ Group by INMSP300.M3PRD Start with this, if it does what you want then add back the max of one column at a time, continuing to retest each time. Good luck Phil
    49,940 pointsBadges:
    report
  • CompEng
    This is bizzar...I pasted the query and in doing so there were a few charactors in the pasting changed. So I corrected them, but when I ran the query the error I got was... of course all the AS400 query infor but the just of the error was an error in the "From Statement" when I clicked the "OK" in the query SQL page it highlighted the word "join". So I typed complete statements into the SQL area and then ran the query and the same error accured. So what that all about?
    1,185 pointsBadges:
    report
  • Kccrosser
    The Join statement doesn't qualify the columns with the table aliases - if either of the columns in the join is in both tables, then the join is ambiguous: From rdb.CARF1.INMSP300 INMSP300 join rdb.CARF1.INMSP100 INMSP100 on M3PRD = MSPRD You may need to use: From rdb.CARF1.INMSP300 INMSP300 join rdb.CARF1.INMSP100 INMSP100 on INMSP300.M3PRD = INMSP100.MSPRD or something similar (I am not sure if I associated the correct table/columns).
    3,830 pointsBadges:
    report
  • philpl1jb
    So .. have you tried the suggestion to qualify the join on field? Since our last try failed, I would try something simplier until we get it to work..perhaps like this. Phil Select INMSP300.M3PRD as “PN”, Count(INMSP300.M3PRD ) as numbers From rdb.CARF1.INMSP300 INMSP300 Where INMSP300.M3CLS =’78′ Group by INMSP300.M3PRD
    49,940 pointsBadges:
    report
  • CompEng
    Yes I ran the query and it worked. It is the little things that get you..It seems like sometimes when you past for the sites to a query page in access some of the grammar signs seem to take on a different form. Then when I run the query it sees the symbol as incorrect. In this case there was a single quotation mark that needed to be a double quotation mark (‘ to “). Then I added the INMSP300 to the part of the WHERE statement and the query ran just great. Thanks again for all the help. Great Job!!!!!!!!
    1,185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following