SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist ghurtado's Avatar
    Join Date
    Sep 2003
    Location
    Wixom, Michigan
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is this possible in MySQL? [problems with COUNT]

    Hi,

    I consider myself a rather proficient mysql user, having used it for rather complicated queries over the years. But on this particular ocassion, after several days trying to figure it out, there has to be something that still escapes me on all of this. Lets start with the query:
    Code:
    SELECT
     customer_orders.customer_order_id AS 'order_id',
     customer_orders.address_book_id AS 'address_id',
     customer_orders_detail.customer_order_detail_id AS 'order_detail_id',
     customer_orders_detail.customer_order_id AS 'order_id',
     customer_orders_detail.product_id AS 'product_id',
     address_book.address_book_id AS 'address_id', address_book.email AS 'email', 
     COUNT( customer_orders.customer_order_id ) AS num_orders
    
    FROM customer_orders
    LEFT JOIN customer_orders_detail
      USING ( customer_order_id )
    LEFT JOIN address_book
      ON customer_orders.address_book_id = address_book.address_book_id
    GROUP BY address_book.email ASC
    What I am trying to do here is to show a row for every customer in the database that shows the number of orders placed per customer (a single customer being defined as all those orders placed with the same email address, hence the GROUP BY), but I am running into the issue that whenever I join the customer_orders_detail table (line items for every order, if you will) to the query, the count returns every single row where there is a matching line item, therefore giving me a count of the total number of line items a customer has purchased, rather than actual number of orders. Thus, if a customer has placed 1 order with 1 line item and another one with 2, the total count is displayed as 3 when it should be 2. The customer_orders_detail table has to be present in the query since there is a filtering on this report that allows the user to restrict the results to certain products purchased, otherwise the query would work like a charm.

    Do you guys have any ideas as to how to tackle this particular issue?

    Thank you very much, I hope I was clear in the description.
    Garcia

  2. #2
    SitePoint Evangelist ghurtado's Avatar
    Join Date
    Sep 2003
    Location
    Wixom, Michigan
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I forgot to mention Im using MySQL 3, so I can't use cross joins or subqueries.
    Garcia

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    CROSS JOINs don't work in version 3? what specific 3.xx.yy version are you actually on? would you be willing to run a quick test for me to determine whether CROSS JOINs work?

    you know, you can always "fake" a cross join like this --
    Code:
    select a.foo, b.bar 
      from table1 as a
         , table2 as b
    also, i think your LEFT OUTER joins don't make sense

    are you expecting customer_orders with no matching customer_orders_detail rows?

    are you expecting customer_orders with no matching address_book rows?

    i would've thought you'd want address_book rows (this is for unique customers, right?) with a LEFT OUTER join to orders, because a customer might not have any orders

    anyhow, about your actual question ...

    instead of COUNT( customer_orders.customer_order_id ),
    try COUNT( distinct customer_orders.customer_order_id )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist ghurtado's Avatar
    Join Date
    Sep 2003
    Location
    Wixom, Michigan
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    instead of COUNT( customer_orders.customer_order_id ),
    try COUNT( distinct customer_orders.customer_order_id )
    YES!!!!! that's exactly what I needed! Thank you very much!


    For some odd reason I thought one could only use DISTINCT right after select, and not with specific fields. That's exactly the answer I was looking for.

    Should I be using straight JOINs in place of LEFT JOIN for customer_orders_detail and address_book?

    Thanks again, you are a blessing to this forum.
    Garcia

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you should be using inner joins only if what you want is just those customers with orders

    if you want customers without orders (you did originally say "a row for every customer in the database"), then you want a LEFT OUTER join where orders is on the right
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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