Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    7

    Unanswered: Problem to combine distinct with count and join

    hi

    i have two tables with shared ID, the shared id in TABLE A is primery key but in TABLE B it is not primery key, and in TABLE B there are more then one record with the shared key.

    i need just the COUNT and SUM of tow of the fields in Table A but its have to be records that the key of them exist in Table B

    with normal query i have no problem to use DISTINCT and solve this like this
    **********************************************
    SELECT
    DISTINCT
    TableA.ID
    FROM
    TableA,
    TableB
    WHERE
    (TableA.ID = TableB.ID)
    *********************************************
    this return only one record even that there are two
    *********************************************

    but what i need is not the records, i need just the COUNT and SUM of tow of the fields in Table A but its have to be records that the key of them exist in Table B, and the problem is that the distinct doesn't effect the count and sum functions even that its only on fields from table A

    the query of the count looks like this:
    *********************************************

    SELECT
    DISTINCT
    COUNT(TableA.ID) AS NumOfOrders,
    SUM(TableA.TotalPrice) AS Total
    FROM
    TableA,
    TableB
    WHERE
    (TableA.ID = TableB.ID)
    ***************************************
    this one return counting and sum of the same record twice just because the id appear two times in table b, the distinct doesn't effect it
    ***************************************

    does any one have an idea how to solve this with out changing the structure or the information of the tables???

    thanks
    jonathan

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT COUNT(TableA.ID) AS NumOfOrders
         , SUM(TableA.TotalPrice) AS Total
      FROM TableA
     where exists
           ( select 937
               from TableB
              WHERE ID = TableA.ID )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    7

    not working

    it seems that any way i am trying to run it i am having an error.
    is it possible that mysql ver is too low i am using 3.23.58????

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is more than just possible, it is certain
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2006
    Posts
    7

    ok but...

    but is there any way to get what i want with my mysql version???

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not in one query, no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2006
    Posts
    7

    ok so....

    thank you very much any way...



Posting Permissions

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