Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Question Unanswered: Subqueries in Access/MSSQL

    Hi,

    To make a long story short, I'm trying to correlate the most inner query (two levels deep) with the most outer one, and it just doesn't work.
    Is it because the correlation must be back-to-back, without any levels that stand before the two?

    If you have no idea what I'm talking about, below is the query that does work, but doesn't produce the right results ("QUOTES" gives the same number for all records):

    ---------------

    select printersGlobal.company, (select count(*) from (select subBidding.bid_company from bidding as subBidding where subBidding.bid_company = 'Printing Company 1' group by subBidding.bid_company, subBidding.project) group by subBidding.bid_company) as QUOTES,

    count(date_picked) as wins

    from printers as printersGlobal

    left outer join bidding as biddingGlobal on printersGlobal.company = biddingGlobal.bid_company
    group by printersGlobal.company
    order by printersGlobal.company asc

    ----------------------

    When I replace 'Printing Company 1' with printersGlobal.company (to correlate and produce dynamic and correct results), I get errors.

    I would appreciate if anybody can point me in the right direction.

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's see if we can figure out what you're doing

    in the innermost query, you are grouping by bid_company and project

    thus, you will get one row per unique bid_company/project combo

    in the query outside of that, you are grouping by subBidding.bid_company, but that's not allowed

    by the time the innermost subquery has run, there is no more subBidding able to group by in the outer subquery

    besides, if you group by anything there, you get an error, because that subquery, the one that produces the COUNT, is allowed to return only one value, and if you were to group by something, then you'd surely get more than one

    if you could explain what you're trying to accomplish i might be able to help more
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    51
    Thanks for your response.

    Here's what I'm trying to accomplish:

    Printers can bid on our company's print projects, and more that one bid can be submitted per project if the company changes their mind regarding the quote/price.

    This is a simplified version of the two tables in question:

    | TABLE: PRINTERS
    | ---------
    | company
    | ---------
    | Company Inc.
    | Printer & Printer
    | Inactive LLC


    | TABLE: BIDDING
    | -------------
    | bid_company --- project -- date_picked
    | --------------------------------------------------------------
    | Printer & Printer -- 66
    | Company Inc. ---- 66 ----- 06/08/2006
    | Printer & Printer -- 67
    | Printer & Printer -- 67


    By using a query I would like to get accurate stats in regard to bid submissions and bid wins:

    | Company ------- Quotes -- Wins
    | --------------------------------------------------------------
    | Printer & Printer --- 2 ------- 0
    | Company Inc. ----- 1 ------- 1
    | Inactive LLC ------ 0 ------- 0

    In other words, Printer & Printer has quoted 2 DIFFERENT projects (although they submitted a total of 3 bids) and they have won 0 projects.
    Company Inc. has quoted 1 different project, and they have won 1 project.
    Inactive LLC has not quoted or won any projects, but the Left Outer Join makes it show up in the results.


    Thanks.

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    You can try something like this

    SELECT B.CompanyID,
    COUNT(DISTINCT(PROJECT)) QUOTES,
    sum(case when picked_date is null then 0 else 1 end) Wins
    FROM dbo.Bidding A
    JOIN dbo.printers B
    ON A.Company_ID = B.CompanyID
    group by B.CompanyID

    SORRY This may not work didn't see that this was access/msssql I don't know if count(distinct will work or if case will work on these platforms
    Last edited by rbackmann; 06-08-06 at 10:36.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, which is it, access or sql server?

    the forum you posted in is sql server, so the rbackmann's query should work

    you can also use this --
    Code:
    count(picked_date) as Wins
    instead of the CASE expression
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    51
    I use Access, however both Access and MSSQL are 100% similar in many cases (unless there's advanced and MSSQL-specific syntax being used).
    I tried this query and Access throws different types of errors (on several parts of the query).

    Here are the errors as I correct them one after another:

    -----------
    Error number: -2147217900
    Error description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'COUNT(DISTINCT(PROJECT)) QUOTES'.

    changed to: ... AS QUOTES
    --------------

    Error number: -2147217900
    Error description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'sum(case when picked_date is null then 0 else 1 end) Wins'.

    changed to: count(picked_date) as Wins

    ----------------

    Error number: -2147217900
    Error description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

    changed to: removed "dbo" references

    ----------------

    Error number: -2147217900
    Error description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

    changed to: "LEFT OUTER JOIN" instead of "JOIN"

    ----------------

    Error number: -2147217900
    Error description: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'DISTINCT' in expression.

    changed to... nothing, I'm stuck

    -----------------------

    This is the final query I used where "DISTINCT" doesn't work:

    SELECT B.CompanyID,
    COUNT(DISTINCT(PROJECT)) as QUOTES,
    count(picked_date) as Wins
    FROM Bidding A
    LEFT OUTER JOIN printers B
    ON A.Company_ID = B.CompanyID
    group by B.CompanyID


    Thanks.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, of course

    DISTINCT doesn't work in access

    so, what are you running, access or sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2004
    Posts
    51
    I connect to an Access database through ASP.

    So, what are the alternatives to COUNT(DISTINCT(PROJECT)) ?

    Thanks.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so can we move this thread to the Access forum, and forget about the SQL Server solution?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2004
    Posts
    51
    I didn't see the Access forum, so I assumed I could post here (since Access and MSSQL are the same most of the time).

    Yes, please move this thread to the apropriate forum.

  11. #11
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    If you are using and access front-end and a SQL server backend connect thru ODBC. You can make this a pass-thru query and it should retunr the record set you want.

    When you say MSSQL do you mean SQL Server of the native query tool in Microsoft.

    Here is the access solution just had to try

    If your tables are in ACCESS or you wish to run this as an Access query


    Q1
    SELECT Sum(IIf(IsNull([dbo_Bidding].[picked_date]),0,1)) AS Wins, dbo_printers.CompanyID
    FROM dbo_Bidding INNER JOIN dbo_printers ON dbo_Bidding.Company_id = dbo_printers.CompanyID
    GROUP BY dbo_printers.CompanyID;


    Q2
    SELECT Company_id, count(*) AS Quotes
    FROM [SELECT dbo_Bidding.Company_id, dbo_Bidding.project
    FROM dbo_Bidding
    GROUP BY dbo_Bidding.Company_id, dbo_Bidding.project]. AS [%$##@_Alias]
    GROUP BY Company_id;


    Q3
    SELECT Query2.Company_id, Query2.Quotes, Query1.Wins
    FROM Query1 INNER JOIN Query2 ON Query1.CompanyID = Query2.Company_id;

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Option 4 is to enable ANSI SQL in Access and then you can use COUNT(DISTINCT MyField) syntax.

    BTW - "Acccess and SQL Server are the same most of the time" is not something many people post in the SQL Server forum more than once
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2004
    Posts
    51
    When I say MS SQL, I mean SQL Server. It's probably not the best way to refer to it.
    I'm using an ODBC connection through ASP.
    Let me try the solutions you provided...

  14. #14
    Join Date
    Mar 2004
    Posts
    51
    "BTW - "Acccess and SQL Server are the same most of the time" is not something many people post in the SQL Server forum more than once "

    I definitely found this to be true in my case. Although there are significant differences behind the scenes, I rarely had the need to use different Query syntax through an ODBC connection. I mostly had to do it when dealing with Oracle and DB2.

    Anyway, I still didn't manage to get the query to work... let me try a few more tweaks...

    Again, this is what I have so far, and "DISTINCT" doesn't work:

    SELECT B.CompanyID,
    COUNT(DISTINCT(PROJECT)) as QUOTES,
    count(picked_date) as Wins
    FROM Bidding A
    LEFT OUTER JOIN printers B
    ON A.Company_ID = B.CompanyID
    group by B.CompanyID
    Last edited by nbozic; 06-08-06 at 12:25.

  15. #15
    Join Date
    Feb 2005
    Posts
    78
    Have you tried DISTINCT PROJECT instead of DISTINCT(PROJECT)?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •