Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count two columns query help

Status
Not open for further replies.
djj55

djj55

Programmer
Joined
Feb 6, 2006
Messages
1,761
Location
US
Hello, SQL 2008
I want to count the number of phone calls by client but also the number of calls by the phone number currently have two queries
Code: 
SELECT Client, PhoneN, Count(*) as PCnt
FROM mytable 
GROUP BY Client, PhoneN

SELECT Client, Count(*) as CCnt 
FROM mytable 
GROUP BY Client
Is there a way to do this in one query? Or would it be a sub query?

Sorry if this does not make sense as I am getting ready to call it a day.

Thanks for any assistance.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!
 
Sort by date Sort by votes
Code: 
SELECT MyTable.*, Tbl1.CCnt
FROM (SELECT mytable.Client,
             mytable.PhoneN,
             Count(*) as PCnt
      FROM mytable
     GROUP BY Client, PhoneN) MyTable
INNER JOIN (SELECT Client,
                   Count(*) as CCnt
            FROM mytable
            GROUP BY Client) Tbl1
ON MyTable.Client = Tbl1.Client


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
SQL Server 2005+:
Code: 
select Client, PhoneN, count(*) over (partition by Client) as cCnt, 
count(*) over (partition by Client, PhoneN) as PCnt 
from myTable 
group by Client, PhoneN

PluralSight Learning Library
 
Upvote 0 Downvote
I haven't used it myself, but I recommend you do some research on the Rollup operator. I think it's exactly what you are looking for.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks everyone.

George your first answer was what I thought would be the answer. I will look into ROLLUP did you mean Grouping or RollupChildren? I will investigate both.

markros I will look into that as I have not used OVER before.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!
 
Upvote 0 Downvote
Here is an example of the Rollup operator.

Code: 
Declare @Temp Table(Client Int, PhoneN VarChar(20))

Insert Into @Temp Values(1, '1-800-SQL-Serv')
Insert Into @Temp Values(1, '1-800-SQL-Serv')
Insert Into @Temp Values(1, '1-800-SQL-Serv')
Insert Into @Temp Values(1, '1-800-DBA-HELP')
Insert Into @Temp Values(1, '1-800-DBA-HELP')

Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Dead')
Insert Into @Temp Values(2, '1-800-VB6-Dead')
Insert Into @Temp Values(2, '1-800-VB6-Dead')

Select Coalesce(Client, -1) As Client, 
       Coalesce(PhoneN, 'All') As PhoneNumber, 
       Count(*) AS PCnt
From   @Temp
Group By Client, PhoneN
With RollUp

I encourage you to copy/paste the code to a query window and take a look at the results. In this case...

[tt]
Client PhoneN PCnt
1 1-800-DBA-HELP 2
1 1-800-SQL-Serv 3
1 All 5
2 1-800-VB6-Dead 3
2 1-800-VB6-Help 6
2 All 9
-1 All 14
[/tt]

Notice that there are extra rows for the totals. If this is not what you want, then don't bother with with Rollup stuff.

If you want to display the Total Count as a separate column then you'll want to use the query that Boris posted.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Interestingly, SQL Server didn't like a mix of OVER and GROUP BY. I re-wrote the query

Code: 
Declare @Temp Table(Client Int, PhoneN VarChar(20))

Insert Into @Temp Values(1, '1-800-SQL-Serv')
Insert Into @Temp Values(1, '1-800-SQL-Serv')
Insert Into @Temp Values(1, '1-800-SQL-Serv')
Insert Into @Temp Values(1, '1-800-DBA-HELP')
Insert Into @Temp Values(1, '1-800-DBA-HELP')

Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Dead')
Insert Into @Temp Values(2, '1-800-VB6-Dead')
Insert Into @Temp Values(2, '1-800-VB6-Dead')
-- markros
select * from (select Client, PhoneN, 
COUNT(*) over (partition by Client) as cCnt, 
COUNT(*) over (partition by Client, PhoneN) as pCnt from @Temp) X group by Client, PhoneN, cCnt, pCnt
--Boris
SELECT MyTable.*, Tbl1.CCnt
FROM (SELECT Client,
             PhoneN,
             Count(*) as PCnt
      FROM @Temp 
     GROUP BY Client, PhoneN) MyTable
INNER JOIN (SELECT Client,
                   Count(*) as CCnt
            FROM @Temp 
            GROUP BY Client) Tbl1
ON MyTable.Client = Tbl1.Client

In the actual execution time Boris query took 16% vs. my 18%

PluralSight Learning Library
 
Upvote 0 Downvote
Boris's can be faster still.

Code: 
--George
SELECT MyTable.Client, 
       MyTable.PhoneN, 
       ClientCounts.CCnt, 
       Count(*) As PCnt
FROM   @Temp MyTable
       INNER JOIN (
          SELECT Client,
                 Count(*) as CCnt
          FROM @Temp MyTable
          GROUP BY Client) ClientCounts
	      ON MyTable.Client = ClientCounts.Client
GROUP BY MyTable.Client, MyTable.PhoneN, ClientCounts.CCnt


Markros = 16%, Boris = 15%, George = 11%

The query using Rollup is faster still (but the output is different so its not a fair comparison).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
There's even a CTE version that produces the same query plan as Boris's. I'm surprised you missed that one.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
No, I didn't miss it - I just can not do more than 10 things at one time.

Answering in ~4 diff. forums, trying to solve battery problems with laptop and finally working as well...

Boris query can also changed to use projection, but I doubt it'll be any faster as usually derived table approach wins.

PluralSight Learning Library
 
Upvote 0 Downvote
Here is another solution which seems to be most performant in my tests:
Code: 
Declare @Temp Table(Client Int, PhoneN VarChar(20))

Insert Into @Temp Values(1, '1-800-SQL-Serv')
Insert Into @Temp Values(1, '1-800-SQL-Serv')
Insert Into @Temp Values(1, '1-800-SQL-Serv')
Insert Into @Temp Values(1, '1-800-DBA-HELP')
Insert Into @Temp Values(1, '1-800-DBA-HELP')

Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Help')
Insert Into @Temp Values(2, '1-800-VB6-Dead')
Insert Into @Temp Values(2, '1-800-VB6-Dead')
Insert Into @Temp Values(2, '1-800-VB6-Dead')
-- markros
select * from (select Client, PhoneN, 
COUNT(*) over (partition by Client) as cCnt, 
COUNT(*) over (partition by Client, PhoneN) as pCnt from @Temp) X group by Client, PhoneN, cCnt, pCnt
--Cross Apply solution

SELECT MyTable.Client, myTable.PhoneN,  F.CCnt, COUNT(*) as pCnt
FROM @Temp MyTable
cross apply (select COUNT(*) as cCnt from @Temp T where T.Client = MyTable.Client) F
group by MyTable.Client, MyTable.PhoneN, F.cCnt  
-- Boris
SELECT MyTable.Client, myTable.PhoneN,  Tbl1.CCnt, COUNT(*) as pCnt
FROM @Temp MyTable
INNER JOIN (SELECT Client,
                   Count(*) as CCnt
            FROM @Temp 
            GROUP BY Client) Tbl1
ON MyTable.Client = Tbl1.Client
GROUP BY myTable.Client, myTable.PhoneN, Tbl1.cCnt

PluralSight Learning Library
 
Upvote 0 Downvote
Thanks all, just got in for the day and have to take care of a couple of things before diving into your information.



djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!
 
Upvote 0 Downvote
Well I went with a modified Boris
Code: 
SELECT A.Client, A.PhoneNum, B.cncount, C.pncount
FROM #Calls A 
LEFT JOIN (SELECT Client, COUNT(*) as cncount
            FROM #Calls 
            GROUP BY Client
) AS B 
ON A.Client = B.Client
LEFT JOIN (SELECT Client, PhoneNum, COUNT(*) as pncount
            FROM #Calls 
            GROUP BY Client, PhoneNum
) AS C 
ON A.Client = C.Client 
    AND A.PhoneNum = C.PhoneNum
Note my final table is a complicated mess that has data repeated several times. I give them what they want even if I think it is stupid.

Thanks for all the help and I have learned some from all the suggestions.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!
 
Upvote 0 Downvote
markros said:
and finally working as well...
Click to expand...
So, when does anyone here have time to do that?[wink]

-------++NO CARRIER++-------
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question Question
Parameterised sql server query
Replies
8
Views
943
Chriss Miller
C
f0rg3tfu1
  • Locked
  • Question Question
PLSQL Package Help
Replies
0
Views
734
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question Question
split one row into multiple rows... help pls
Replies
12
Views
2K
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question Question
Error in Select query
Replies
14
Views
1K
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
534
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top