Count

Question

Post reply

Count

Anjan@Sql

SSCrazy

Points: 2316
More actions
November 4, 2013 at 11:05 am

#300736

Hi I Have A Table with 6 columns.
Select count(*)
from
(
Select Column1,Column2,Count(*) From Table1
group by Column1,Column2
)
<------------------------------------------------>
Select Count(*) from
(
Select T1.Column1,T1.Column2,T1.Column3,T1.Column4,T1.Column5,T1.Column6
From Table1 T1,
(
Select T2.Column1 a,T2.Column2 b,Count(*) From Table1 T2
group by Column1,Column2
)dev
where dev.a=T1.Column1 and dev.b=T1.Column2
)ab
Is there Any diff b/w two queries.if no then why i am getting diff count value?pls clarify me.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Yes, there's a difference and you're duplicating rows on the second statement due to the join.

I suggest you to change your joins to ANSI-92 standard (using JOIN keywords).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 2

Select Count(*) from

(

Select T1.Column1,T1.Column2,T1.Column3,T1.Column4,T1.Column5,T1.Column6

From Table1 T1 Inner join

(

Select T2.Column1 a,T2.Column2 b,Count(*) From Table1 T2

group by Column1,Column2

)dev

on dev.a=T1.Column1 and dev.b=T1.Column2

)ab

Hi Luis you are saying something like this.i tried this but not getting same count.pls assist me....

Sean Lange SSC Guru Points: 286589 More actions · Answer 3

This is where you post ddl and sample data for your problem. Please take a few minutes and read the article in my signature about how to post questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/