CODEWITHSUNDEEP
mysqlsql
Fazz
Fazz

Reputation: 15

Is there anyway to perform a Count() when using Min()?

The statement below is to get all non-duplicate IDs from the products table. Is there a way I can get the total count of rows that are outputted by this sql statement?

select min(product_id) from products 
where market_code = 'germany'
group by product_code


sample table data:

product_id   market_code   product_code
   1            uk             AAA
   1            uk             AAA
   1            uk             AAA
   2           germany         BAA
   2           germany         BAA
   3            uk             BAA

Thanks

Upvotes: 1

Views: 69

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

The statement:

select min(product_id)
from products 
where market_code = 'germany'
group by product_code;

is going to have as many rows as there are product code values in Germany. If you assume that a given product id never has two codes (which is true of your sample data), then the following counts the products in Germany without using product id at all:

select count(distinct product_code)
from products
where market_code = 'germany';

Upvotes: 0

ogres
ogres

Reputation: 3690

Actually your query is not what you said "The statement below is to get all unique/non-duplicate IDs from the products table." , It will select lowest product_id for product_code , not unique , for example if there are product_id - 2 and product_id - 3 for product_code A , it will only return product_id - 2 , it is a minimum value not unique , if you want unique you could do this way

SELECT product_code,product_id,count(*) 
FROM TABLE 
GROUP BY product_code,product_id

if you want just unique/non-duplicate ID-s and their count you can select with this query

SELECT product_id,count(*) 
    FROM table 
    GROUP BY product_id

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can simply do this:

SELECT COUNT(*)
FROM
(
    select min(product_id) from products 
    where market_code = 'germany'
    group by product_code
) AS t;

Upvotes: 1

Related Questions