Getting count of distinct merchants to products

[afrika]

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

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

[r937]

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

[afrika]

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