Count with Group By

30 Sep 2005 9:01 AM

kiran

Hi,

I am using GROUP BY on two columns.

SELECT ID,T_ID FROM EMPLOYEES GROUP BY ID,T_ID

Result:

154    1
154    2
154    3
154    6
154    7
154    8
154    9
154    10
161    11

I tried

SELECT COUNT(ID) FROM EMPLOYEES GROUP BY ID,T_ID

Result:

1
1
3
3
6
1
1
1
2

But I was expecting a result like this

Result:

8
1

Can someone point me out how to get the expected result

Thanks
Kiran

30 Sep 2005 9:13 AM

David Portas

SELECT COUNT(DISTINCT t_id) AS cnt
FROM Employees
GROUP BY id ;

--
David Portas
SQL Server MVP
--

30 Sep 2005 3:17 PM

kiran

David Portas wrote:

> SELECT COUNT(DISTINCT t_id) AS cnt
> FROM Employees
> GROUP BY id ;
>

Thanks David, It worked

30 Sep 2005 9:26 AM

Karthik

try this

select count(ID) as IDCount from employees group by ID

Cheers!

Show quote

"kiran" wrote:

> Hi,
>
> I am using GROUP BY on two columns.
>
> SELECT ID,T_ID FROM EMPLOYEES GROUP BY ID,T_ID
>
> Result:
>
> 154    1
> 154    2
> 154    3
> 154    6
> 154    7
> 154    8
> 154    9
> 154    10
> 161    11
>
> I tried
>
> SELECT COUNT(ID) FROM EMPLOYEES GROUP BY ID,T_ID
>
> Result:
>
> 1
> 1
> 3
> 3
> 6
> 1
> 1
> 1
> 2
>
> But I was expecting a result like this
>
> Result:
>
> 8
> 1
>
> Can someone point me out how to get the expected result
>
> Thanks
> Kiran
>

30 Sep 2005 11:47 AM

R.D

or if you want both values
SELECT ID,COUNT(*) AS Noofpeople FROM employees group by ID
Regards
R.D

Show quote

"Karthik" wrote:

> try this
>
> select count(ID) as IDCount from employees group by ID
>
> Cheers!
>
> "kiran" wrote:
>
> > Hi,
> >
> > I am using GROUP BY on two columns.
> >
> > SELECT ID,T_ID FROM EMPLOYEES GROUP BY ID,T_ID
> >
> > Result:
> >
> > 154    1
> > 154    2
> > 154    3
> > 154    6
> > 154    7
> > 154    8
> > 154    9
> > 154    10
> > 161    11
> >
> > I tried
> >
> > SELECT COUNT(ID) FROM EMPLOYEES GROUP BY ID,T_ID
> >
> > Result:
> >
> > 1
> > 1
> > 3
> > 3
> > 6
> > 1
> > 1
> > 1
> > 2
> >
> > But I was expecting a result like this
> >
> > Result:
> >
> > 8
> > 1
> >
> > Can someone point me out how to get the expected result
> >
> > Thanks
> > Kiran
> >