Hi to all!
To count(something), it is usually required to use a group by function, however, that is not applicable in this case: I would like to select records that have similar values for 'col2' and 'col3', given a certain 'col1', and how many records each 'col1' returns. The following example hopefully makes it clear.
The table:
Col1 Col2 Col3
s1 A B
s1 C D
s2 A B
s2 C D
The result should be:
col1 col2 col3 Expr
s2 A B 2
s2 C D 2
I had figured out the not-working-query:
SELECT *, count(col1)
FROM table as t1 INNER JOIN table as t2 ON
t1.col2 = t2.col2 AND t1.col3 = t3.col3
WHERE col1 = 's1'
Could anyone provide me a hand? Many thanks in advance.
Kind regards,
Nele
SELECT Col1,Col2,Col3,Count(Col1)
FROM TEST
GROUP BY Col1,Col2,Col3
|||No, that returns for 'expr' value 1.
|||Are you trying to find those records where value of COL2 and COl3 are similar, which means there is more than one record having same value for col2 and col3
Col1 Col2 Col3
s1 A B
s1 C D
s2 A B
s2 C D
s3 C A
IF I get it correctly, here the last record should not be displayed. Is that what you intend?
|||
In SQL 2000, you can do:
select *, (select count(*) from table as t2 where t2.col1 = 's1') as cnt
from table as t1
where t1.col1 = 's1'
In SQL 2005, you can do:
select *, count(*) over(order by t1.col1) as cnt
from table as t1
where t1.col1 = 's1'
Having said this, there is really no need to complicate the query by doing this. You will only get slow performance. The number of rows that qualifies the query can be obtained by looking at @.@.ROWCOUNT after the SELECT statement or determined in the client side easily. So it seems unnecessary to do this .
|||Yes! That is what I intend.|||I tried your code, but for sql 2005 it returned an error "incorrect syntax near the keyword 'order' "?
@.@.rowcount returns the total number, I rather would like to know how many records of a certain value for 'col1' are returned (to select the top(100) in the end). E.g., for 's2' that is 2.
|||How's about:
Select Col2, Col3, Count(distinct col1 )
From ...
Group by Col2, Col3
Having Count(distinct col1) > 1
And if you want Col1 to display, you can add use
Select Max(Col1), Col2, Col3, Count(distinct col1)
|||Sorry that should be partition by not order by. We don't support ORDER BY clause for aggregate function yet in OVER clause. We support only partition clause.
|||No, the problem is that I want to know how many times a certain value for 'col1' occurs, not taking into account the values of col2 and col3 for that calculation.
Besides, all have an nvarchar as type.
|||Yes, that is what I wanted to achieve 
Thanks for the reactions!
sql