count rows - 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

General SQL Server Forums

New to SQL Server Programming

count rows

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-01-19 : 14:37:37

hi.I have a SP like this:

CREATE PROCEDURE spGetLevel2ByName (
@LEVEL2_STRIPPED varchar(150)
)
AS

SELECT DISTINCT
z.cat_level3_id, z.cat_level2_id, b.cat_level2_name, b.cat_level2_stripped, b.cat_level2_image, c.cat_level3_name, c.cat_level3_stripped, k.Keyword,
p.product_id, pp.product_price, p.product_name, p.product_stripped, p.product_current_wholesale_price, p.product_price AS Expr1,
p.product_current_price, p.product_msrp
FROM dbo.tbl_configurator z INNER JOIN
dbo.tbl_cat_level1 a ON z.cat_level1_id = a.cat_level1_id INNER JOIN
dbo.tbl_cat_level2 b ON z.cat_level2_id = b.cat_level2_id AND b.cat_level2_stripped = @LEVEL2_STRIPPED
INNER JOIN
dbo.tbl_cat_level3 c ON z.cat_level3_id = c.cat_level3_id INNER JOIN
dbo.tbl_product p ON z.product_id = p.product_id LEFT OUTER JOIN
dbo.tbl_keyword k ON p.product_id = k.product_id INNER JOIN
dbo.tbl_product_price pp ON p.product_id = pp.product_id AND pp.product_price_level = 0
WHERE (p.product_active = 1)
GO
and I've got these results from it:

cat_level3_id cat_level2_id product_id
56772 1297 17525 ...
56772 1297 13703 ...
56772 1297 16696 ...
57573 1297 16697 ...
57573 1297 15556 ...
.
.
.

how can I count the number of rows for cat_level3_id on my query?I mean I need to have these results:
for cat_level3_id: 56772 count=3
for cat_level3_id: 57573 count=2
Ive tried count & group by but didn't work.
plz help Im new :(
thanks in Advanced

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-19 : 14:44:23

Do you want it included in the returned result set or do you want a second result set returned from the stored procedure?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-19 : 14:44:48

Also, what version of SQL Server are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-01-19 : 14:51:23

thanks for yr reply.Im using sql 2000,and I'd like to have it included in the returned result set but its also ok if I can have it as a second result set returned from the stored procedure.
thank u so much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-19 : 14:56:48

The best approach would be to do the count in your application once it has received the returned result set, but if that's not an option, then I'd use a second result set. Be warned though that this is a performance issue:

SELECT z.cat_level3_id, COUNT(*) AS CatLevel3Count
FROM dbo.tbl_configurator z
INNER JOIN dbo.tbl_cat_level1 a
ON z.cat_level1_id = a.cat_level1_id
INNER JOIN dbo.tbl_cat_level2 b
ON z.cat_level2_id = b.cat_level2_id AND
b.cat_level2_stripped = @LEVEL2_STRIPPED
INNER JOIN dbo.tbl_cat_level3 c
ON z.cat_level3_id = c.cat_level3_id
INNER JOIN dbo.tbl_product p
ON z.product_id = p.product_id
LEFT OUTER JOIN dbo.tbl_keyword k
ON p.product_id = k.product_id
INNER JOIN dbo.tbl_product_price pp
ON p.product_id = pp.product_id AND pp.product_price_level = 0
WHERE (p.product_active = 1)
GROUP BY z.cat_level3_id
GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-01-19 : 15:05:02

thanks it worked :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-19 : 15:14:06

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-01-19 : 15:21:29

Try the following to see everything in One line...

regards,
Anil Kumar.

SELECT DISTINCT
z.cat_level3_id, B.Number, z.cat_level2_id, b.cat_level2_name, b.cat_level2_stripped, b.cat_level2_image, c.cat_level3_name, c.cat_level3_stripped, k.Keyword,
p.product_id, pp.product_price, p.product_name, p.product_stripped, p.product_current_wholesale_price, p.product_price AS Expr1,
p.product_current_price, p.product_msrp
FROM dbo.tbl_configurator z INNER JOIN
dbo.tbl_cat_level1 a ON z.cat_level1_id = a.cat_level1_id INNER JOIN
dbo.tbl_cat_level2 b ON z.cat_level2_id = b.cat_level2_id AND b.cat_level2_stripped = @LEVEL2_STRIPPED
INNER JOIN
dbo.tbl_cat_level3 c ON z.cat_level3_id = c.cat_level3_id INNER JOIN
dbo.tbl_product p ON z.product_id = p.product_id LEFT OUTER JOIN
dbo.tbl_keyword k ON p.product_id = k.product_id INNER JOIN
dbo.tbl_product_price pp ON p.product_id = pp.product_id

join

(select A.Cat_level3_id, count(*) as Number from
(SELECT DISTINCT
z.cat_level3_id, z.cat_level2_id, b.cat_level2_name, b.cat_level2_stripped, b.cat_level2_image, c.cat_level3_name, c.cat_level3_stripped, k.Keyword,
p.product_id, pp.product_price, p.product_name, p.product_stripped, p.product_current_wholesale_price, p.product_price AS Expr1,
p.product_current_price, p.product_msrp
FROM dbo.tbl_configurator z INNER JOIN
dbo.tbl_cat_level1 a ON z.cat_level1_id = a.cat_level1_id INNER JOIN
dbo.tbl_cat_level2 b ON z.cat_level2_id = b.cat_level2_id AND b.cat_level2_stripped = @LEVEL2_STRIPPED
INNER JOIN
dbo.tbl_cat_level3 c ON z.cat_level3_id = c.cat_level3_id INNER JOIN
dbo.tbl_product p ON z.product_id = p.product_id LEFT OUTER JOIN
dbo.tbl_keyword k ON p.product_id = k.product_id INNER JOIN
dbo.tbl_product_price pp ON p.product_id = pp.product_id AND pp.product_price_level = 0
WHERE (p.product_active = 1))A
Group by Cat_level3_id)B
on B.Cat_level3_id = z.cat_level3_id

WHERE (p.product_active = 1) AND pp.product_price_level = 0

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-01-19 : 15:27:45

Dear Kumar I've tried yr query but gave me the error: the correlation name 'B' is specified multiple times in a form clause :(

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-01-19 : 15:36:39

Sorry I didnt see that the alias "B." is already under use. Try this now & let me know.

regards,
Anil Kumar.

SELECT DISTINCT
z.cat_level3_id, PQ.Number, z.cat_level2_id, b.cat_level2_name, b.cat_level2_stripped, b.cat_level2_image, c.cat_level3_name, c.cat_level3_stripped, k.Keyword,
p.product_id, pp.product_price, p.product_name, p.product_stripped, p.product_current_wholesale_price, p.product_price AS Expr1,
p.product_current_price, p.product_msrp
FROM dbo.tbl_configurator z INNER JOIN
dbo.tbl_cat_level1 a ON z.cat_level1_id = a.cat_level1_id INNER JOIN
dbo.tbl_cat_level2 b ON z.cat_level2_id = b.cat_level2_id AND b.cat_level2_stripped = @LEVEL2_STRIPPED
INNER JOIN
dbo.tbl_cat_level3 c ON z.cat_level3_id = c.cat_level3_id INNER JOIN
dbo.tbl_product p ON z.product_id = p.product_id LEFT OUTER JOIN
dbo.tbl_keyword k ON p.product_id = k.product_id INNER JOIN
dbo.tbl_product_price pp ON p.product_id = pp.product_id

join

(select MN.Cat_level3_id, count(*) as Number from
(SELECT DISTINCT
z.cat_level3_id, z.cat_level2_id, b.cat_level2_name, b.cat_level2_stripped, b.cat_level2_image, c.cat_level3_name, c.cat_level3_stripped, k.Keyword,
p.product_id, pp.product_price, p.product_name, p.product_stripped, p.product_current_wholesale_price, p.product_price AS Expr1,
p.product_current_price, p.product_msrp
FROM dbo.tbl_configurator z INNER JOIN
dbo.tbl_cat_level1 a ON z.cat_level1_id = a.cat_level1_id INNER JOIN
dbo.tbl_cat_level2 b ON z.cat_level2_id = b.cat_level2_id AND b.cat_level2_stripped = @LEVEL2_STRIPPED
INNER JOIN
dbo.tbl_cat_level3 c ON z.cat_level3_id = c.cat_level3_id INNER JOIN
dbo.tbl_product p ON z.product_id = p.product_id LEFT OUTER JOIN
dbo.tbl_keyword k ON p.product_id = k.product_id INNER JOIN
dbo.tbl_product_price pp ON p.product_id = pp.product_id AND pp.product_price_level = 0
WHERE (p.product_active = 1))MN
Group by Cat_level3_id)PQ
on PQ.Cat_level3_id = z.cat_level3_id

WHERE (p.product_active = 1) AND pp.product_price_level = 0

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-01-19 : 16:14:09

thanks works :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources