SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting count of distinct merchants to products

    hello,
    I have the query below. To retrieve the number of merchants in the merchants table and the number of related products each merchant has.

    Now when i run this query, i get the merchants in table a
    2. the total number of products repeated in table b. (THE SAME FOR ALL MERCHANTS)

    How do i get each distinct merchant products assigned to his name ?

    something like this

    MERCHANT PRODUCT

    Afrika 45
    Sony 23
    Nokia 59

    etc

    thanks
    Afrika

    Code:
    SELECT     a.merchants, b.products
    FROM         (SELECT DISTINCT (merchant) merchants
                           FROM          dbo.markets_product_registration) a CROSS JOIN
                              (SELECT     COUNT(products) AS products
                                FROM          dbo.markets_products
                                WHERE      (status = 1)) b

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm not sure i understand what that query is doing, so let's go back to basics, your requirements --

    "To retrieve the number of merchants in the merchants table and the number of related products each merchant has."

    that doesn't make sense -- the number of merchants is one number, so you're looking for a one-row answer?

    maybe the total number of merchants plus the total number of all products that they have?

    "How do i get each distinct merchant products assigned to his name?"

    with a GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937
    To retrieve the number of merchants in the merchants table and the number of related products each merchant has."

    that doesn't make sense -- the number of merchants is one number, so you're looking for a one-row answer?
    Am sorry Rudy, i made a mistake trying to explain. As you said below

    maybe the total number of merchants plus the total number of all products that they have?

    "How do i get each distinct merchant products assigned to his name?"

    with a GROUP BY
    is what i was trying to explain.

    I just got it right below. Thanks

    CODE:
    SELECT a.merchant, count(*) AS products
    FROM dbo.markets_product_registration a
    INNER JOIN dbo.markets_products b ON a.product_registration=b.products
    WHERE b.status=1
    GROUP BY a.merchant
    Have a lovely weekend

    afrika


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
  •