CODEWITHSUNDEEP
sqlsql-server
Freddakos
Freddakos

Reputation: 107

Duplicated records from SELECT DISTINCT COUNT statement

I have 2 tables with the following structure:

 ------------------------------------
| dbo.Katigories  | dbo.Products     |
|-----------------|------------------|
| product_id      | product_id       |
| Cat_Main_ID     | F_material       |
| Cat_Sub_ID      |                  |

What I am trying to accomplish is the following:

I want to COUNT how many unique products (from the Products table) have as Cat_Main_ID=111, have as Cat_Sub_ID=222, and have as F_material=10

I have tried the following SELECT COUNT statement:

SELECT DISTINCT COUNT(*) AS METR_AN_EXEI_001
FROM dbo.Products P
INNER JOIN dbo.Katigories K
ON P.product_id = K.product_id
AND
K.Cat_Main_ID = 111
AND
P.F_material = 10
AND
K.Cat_Sub_ID = 222

The above statement is working, and counts the correct products, but is giving me duplicated records. For example: When a product belongs to only 1 category the result of my count is correct and I am getting the number 1 as a result. But when a product belongs to more than one category then the result of the count is incorrect, depending on how many categories the product belongs. I know that the reason for the duplicates is that some of my products belong simultaneously to more than one category or sub category, so I am getting incorrect count results.

It will be truly appreciated if someone could help me with the syntax of my COUNT statement, in order to get the correct number of products (without duplicates) from my Products table.

!!!!!!!!!!!!!-----------------------------------------!!!!!!!!!!

Dear all.

Please forgive me for the inconvenience!

I'm so stupid, that I was placing your code in the wrong point in my page. Eventually almost all of the suggestions were correct.

Now I have a problem and I do not know which of the answers I choose to be right. All of them are correct!

A very big thank to all of you and I apologize again to all of you.

Upvotes: 0

Views: 64

Answers (3)

Ermindo Lopes
Ermindo Lopes

Reputation: 109

Can u try it using subselect to count, like this:

DECLARE @Katigories TABLE ( product_id int , Cat_Main_ID int , Cat_Sub_ID int )
DECLARE @Products TABLE( product_id int , F_material int )

INSERT INTO @Products (product_id,F_material) VALUES (1, 10)
INSERT INTO @Products (product_id,F_material) VALUES (2, 10)
INSERT INTO @Products (product_id,F_material) VALUES (3, 15)

INSERT INTO @Katigories ( product_id ,Cat_Main_ID ,Cat_Sub_ID ) VALUES  ( 1,111, 222 )
INSERT INTO @Katigories ( product_id ,Cat_Main_ID ,Cat_Sub_ID ) VALUES  ( 1,123, 223 )
INSERT INTO @Katigories ( product_id ,Cat_Main_ID ,Cat_Sub_ID ) VALUES  ( 1,444, 222 )
INSERT INTO @Katigories ( product_id ,Cat_Main_ID ,Cat_Sub_ID ) VALUES  ( 2,133, 223 )

SELECT 
    P.product_id,
    (SELECT COUNT(*) FROM @Katigories WHERE product_id = P.product_id AND Cat_Main_ID=111 AND Cat_Sub_ID=222 AND P.F_material=10) AS METR_AN_EXEI_001
FROM @Products P

Upvotes: 1

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

If you need to count distinct products use count(distinct ...). Something like this.

SELECT COUNT(DISTINCT P.product_id) AS METR_AN_EXEI_001
FROM dbo.Products P
INNER JOIN dbo.Katigories K
ON P.product_id = K.product_id
AND
K.Cat_Main_ID = 111
AND
P.F_material = 10
AND
K.Cat_Sub_ID = 222

Upvotes: 0

Samuel A C
Samuel A C

Reputation: 466

I suppose grouping should be approporate compared to distinct in this case

 SELECT product_id, COUNT(product_id)  
 METR_AN_EXEI_001
 FROM dbo.Products P
 INNER JOIN dbo.Katigories K
 ON P.product_id = K.product_id
 AND
  K.Cat_Main_ID = 111
 AND
 P.F_material = 10
 AND
  K.Cat_Sub_ID = 222
  GROUP BY P.product_id

Upvotes: 2

Related Questions