|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
please help on count(distinct <expression>)Hi all,
the sql parser does not like when trying to execute a query that has more than one column to be distinct within the count function. i.e. select count ( distinct a.ssn, a.term ) from mytable when I just leave one column (i.e. ssn), the count is executed normally, but it does not like to consider more columns. Does someone knows why? Thanks in advance, Carlos. Change it to:
SELECT DISTINCT a.SSN , a.Term , count(1) AS 'Total Distinct Values' FROM Mytable -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "Carlos" <ch_sa***@yahoo.com> wrote in message news:eW2BWW3pGHA.3908@TK2MSFTNGP05.phx.gbl... > > > Hi all, > > the sql parser does not like when trying to execute a query that > has more than one column to be distinct within the count function. > i.e. > > select count ( distinct a.ssn, a.term ) from > mytable > > when I just leave one column (i.e. ssn), the count is executed normally, > but it does not like to consider more columns. > > Does someone knows why? > > Thanks in advance, > > Carlos. > > That's because COUNT takes exactly one argument.
How about SELECT COUNT(*) FROM (SELECT ssn, term FROM mytable GROUP BY ssn, term) x Show quote "Carlos" <ch_sa***@yahoo.com> wrote in message news:eW2BWW3pGHA.3908@TK2MSFTNGP05.phx.gbl... > > > Hi all, > > the sql parser does not like when trying to execute a query that > has more than one column to be distinct within the count function. > i.e. > > select count ( distinct a.ssn, a.term ) from > mytable > > when I just leave one column (i.e. ssn), the count is executed normally, > but it does not like to consider more columns. > > Does someone knows why? > > Thanks in advance, > > Carlos. > Thank you all for your help. SQL server books online describes that
an expression can acompany the count, so I thought that it would be possible. I used something similar to Aaron's answer and it succeeded. Thanks again! Carlos. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%234n20b3pGHA.4116@TK2MSFTNGP03.phx.gbl... > That's because COUNT takes exactly one argument. > > How about > > SELECT COUNT(*) FROM (SELECT ssn, term FROM mytable GROUP BY ssn, term) x > > > > "Carlos" <ch_sa***@yahoo.com> wrote in message > news:eW2BWW3pGHA.3908@TK2MSFTNGP05.phx.gbl... >> >> >> Hi all, >> >> the sql parser does not like when trying to execute a query that >> has more than one column to be distinct within the count function. >> i.e. >> >> select count ( distinct a.ssn, a.term ) from >> mytable >> >> when I just leave one column (i.e. ssn), the count is executed normally, >> but it does not like to consider more columns. >> >> Does someone knows why? >> >> Thanks in advance, >> >> Carlos. >> > >  |
|||||||||||||||||||||||
Other interesting topics