How to Group by....

15 Dec 2005 9:50 PM

JDP@Work

I'm trying to get a count of salesmanagers for a selected director from a
listing of Sales.

This is a one of many columns that I'm returning, but I need this ONE to be
right.

I want to get a count for a given dos as...

dos = 2 (there are only two smg's for this dos)
sod = 1

-- ddl begin copy section....
create table #tmp (id uniqueidentifier ,smg varchar(1) ,dos varchar(4))
insert #tmp select newid() ,'A' ,'DOS'
insert #tmp select newid() ,'A' ,'DOS'
insert #tmp select newid() ,'B' ,'DOS'
insert #tmp select newid() ,'C' ,'SOD'

-- bad attempt 1
select dos
,count(smg)
from #tmp
where dos = 'dos'
group by dos

-- bad attempt 2
select count(dos) from #tmp where dos = 'dos' group by smg

select * from #tmp
drop table #tmp

--- end copy section....

TIA

JeffP....

15 Dec 2005 9:51 PM

JI

I think all you have to do is remove the where clause.

select dos,count(1) dosCount
from #tmp
group by dos

Show quote

"JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
news:%23x$B%23AcAGHA.3976@TK2MSFTNGP09.phx.gbl...
> I'm trying to get a count of salesmanagers for a selected director from a
> listing of Sales.
>
> This is a one of many columns that I'm returning, but I need this ONE to
> be
> right.
>
> I want to get a count for a given dos as...
>
> dos = 2 (there are only two smg's for this dos)
> sod = 1
>
>
> -- ddl begin copy section....
> create table #tmp (id uniqueidentifier ,smg varchar(1) ,dos varchar(4))
> insert #tmp select newid() ,'A' ,'DOS'
> insert #tmp select newid() ,'A' ,'DOS'
> insert #tmp select newid() ,'B' ,'DOS'
> insert #tmp select newid() ,'C' ,'SOD'
>
> -- bad attempt 1
> select dos
> ,count(smg)
> from #tmp
> where dos = 'dos'
> group by dos
>
> -- bad attempt 2
> select count(dos) from #tmp where dos = 'dos' group by smg
>
> select * from #tmp
> drop table #tmp
>
> --- end copy section....
>
> TIA
>
> JeffP....
>
>

15 Dec 2005 9:53 PM

--CELKO--

SELECT COUNT(DISTINCT smg), dos
FROM Foobar
GROUP BY dos;

16 Dec 2005 8:27 AM

Tony Rogerson

> SELECT COUNT(DISTINCT smg), dos

What on earth is COUNT( DISTINCT smg ) on the results? You should name
columns, a real programmer will know that.

Go back and learn your fundementals.

You should name it using one of these methods depending on your
preference...

SELECT sales_director_count = COUNT(DISTINCT smg)

OR

SELECT COUNT(DISTINCT smg) AS sales_director_count

I prefer the former because it lends itself to proper indentation on the
select statement so you can actually find the expression that the column is
derived from.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1134683614.842066.236590@g44g2000cwa.googlegroups.com...
> SELECT COUNT(DISTINCT smg), dos
> FROM Foobar
> GROUP BY dos;
>

17 Dec 2005 9:58 PM

05ponyGT

"Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message
news:OYcHtnhAGHA.3456@TK2MSFTNGP11.phx.gbl...
> What on earth is COUNT( DISTINCT smg ) on the results? You should name
> columns, a real programmer will know that.

Why does this even execute?

> Go back and learn your fundementals.

Primary target of this thought should be rdms vendors!
Perhaps Sql server 2010 ?:)

Happy Holidays from:
www.rac4sql.net