GROUP BY - COUNT ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

GROUP BY - COUNT ?

Author

Topic

bo92
Starting Member

1 Post

Posted - 2003-09-25 : 15:11:30

Hey guys!

I was wondering if you could help me. It shouldn't take too long for an expertto answer my question...so here we go:

This SQL string:

SELECT fk_brand, COUNT(*) AS count_items FROM items GROUP BY (fk_brand)

work's just fine, but if I want to plug more data into it stops...totally :-(

fx. this doesn't work properly:

SELECT itemname, fk_brand, COUNT(*) AS count_items FROM items GROUP BY (fk_brand)

And this is exactly what I need help to do.

How do pull out "itemname" along with the other data in my SQL2000 database table?

Thanks for now

/Daniel

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-09-25 : 15:39:00

Use a derived table of your count and brand column and rejoin back to the original table.


create table items
(
	itemname varchar(10),
	brand varchar(10)
)
go
insert into items
values ('Inspiron', 'Dell')
insert into items
values ('Vectra', 'HP')
insert into items
values ('Kayak', 'HP')
insert into items
values ('PowerEdge', 'Dell')
insert into items
values ('Dimension', 'Dell')
insert into items
values ('Optiplex', 'Dell')
insert into items
values ('Precision', 'Dell')
go
select brand, count(*) brandcount from items group by brand
go
select itemname, i1.brand, brandcount
from (select brand, count(*) brandcount from items group by brand) i1
inner join items i2 on i1.brand = i2.brand

hth,
Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-25 : 15:39:00

SELECT

(select top 1 i.itemname from items i
where i.fk_brand=items.fk_brand) AS iname,

fk_brand, COUNT(*) AS count_items

FROM items GROUP BY (fk_brand)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-25 : 16:19:10

Not so bad... Two solutions.
The first one is for the case of different itemnames
within each fk_brand group and the second - for the case
of the same itemnames in each fk_brand group.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-25 : 16:46:28

Plus, there is the third case - when item names are different but it
does not matter which one should be retrieved as a match for a fk_brand.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-09-25 : 16:53:12

quote:
the second - for the case
of the same itemnames in each fk_brand group.

I'm not so sure that the correlated sub query would work. For one thing if the item names are the same (for example Brand - Dell ItemName - Inspiron and the differentiator was the series # a la 1100 and 5100) then the itemname could simply be used in the group by without any problem. However if bo92 now wants to inlcude the Price the top 1 correlated subquery solution is going to pull only a single price as opposed to all prices. I think this is where he will see the difference in behavior

With top 1 solution:
Inspiron, Dell, 2, $1500.00

With the derived table:
Inspiron, Dell, 2, $1500.00
Inspiron, Dell, 2, $1750.00

m2c,
Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-09-25 : 16:57:10

quote:
Originally posted by Stoad

Plus, there is the third case - when item names are different but it
does not matter which one should be retrieved as a match for a fk_brand.

That was my concern with your solution (and the reason for my subsequent post.) But if bo92 just wants a sapmpling of the data then the top 1 solution works fine too.

Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-26 : 02:38:38

Exactly, Justin!

But, to be honest, from the very beginning I took into my consideration
only this 'last', the third case (as you put it: 'just wants a sapmpling of the data').

Otherwise, a bit embarrassing excessiveness in output column brandCount presents.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources