Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Comparing row counts and displaying results from greatest row count from a single table Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 8:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:17 PM
Points: 8, Visits: 41
insert into CallData ([800num], CompanyName) values ('8009874321', 'cars');
insert into CallData ([800num], CompanyName) values ('8009874321', 'Newsales');
insert into CallData ([800num], CompanyName) values ('8009874321', 'Newsales');
insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales');
insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales');
insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales2');
insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales2');

Requirement 1: When the 800num matches I would like to return a single row, the row with the greatest row count. Requirement 2: If the 800num row count matches then return both rows.

Results would be:

8009874321 Newsales
8009870000 BenaSales
8009870000 BenaSales2
Post #1488528
Posted Monday, August 26, 2013 9:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:43 PM
Points: 1,282, Visits: 1,762
How about this?

select max(x.row) rows, x.[800num], x.CompanyName
from (
select row_number() OVER (partition by a.CompanyName order by a.[800num]) row, a.[800num], a.CompanyName
from calldata a ) as x
group by x.[800num], x.CompanyName

Post #1488537
Posted Monday, August 26, 2013 9:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:17 PM
Points: 8, Visits: 41
Thanks for the reply, I only want the results to show the 800 number with the greatest row count.
Post #1488541
Posted Monday, August 26, 2013 10:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:17 PM
Points: 8, Visits: 41
Here's what I've come up with:
select distinct [800number], (
select top 1 CompanyName
from calldata
where calldata.[800number]=cd.[800number]
group by [800number], CompanyName
order by [800number], count(*) desc) as companyname
from calldata cd

My issue is this solution doesn't list companyname that have matching row counts. Meaning is 8001231234 "newcars" as 3 rows and 8001231234 "cars" has 3 rows only one of those will be listed. I wanted both of them listed because they have the same 800 number - 8001231234. Any ideas how to rewrite the query to accomplish this requirement?
Post #1488543
Posted Tuesday, August 27, 2013 3:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
;WITH SampleData AS (
SELECT * FROM (values
('8009874321', 'cars'),
('8009874321', 'Newsales'),
('8009874321', 'Newsales'),
('8009870000', 'Newsales'),
('8009870000', 'BenaSales'),
('8009870000', 'BenaSales'),
('8009870000', 'BenaSales2'),
('8009870000', 'BenaSales2')
) sampleData ([800num], CompanyName))

SELECT [800num], CompanyName
FROM (
SELECT [800num], CompanyName,
dr = DENSE_RANK() OVER(PARTITION BY [800num] ORDER BY cnt DESC)
FROM (
SELECT [800num], CompanyName, cnt = COUNT(*)
FROM SampleData
GROUP BY [800num], CompanyName
) d
) e
WHERE dr = 1



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1488645
Posted Tuesday, August 27, 2013 9:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:17 PM
Points: 8, Visits: 41
Amazing how fast this query works...Thank you!
Post #1488845
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse