• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

SQL 2005 - Count then display group items in a table.

Looking for a way to count group items in a table and then display them per
ranking..ie from most to least.

Not getting much success with count or SUM.

One main problem is that I have 3 columns,,DIF, CallID, LoginID...I am able to group the colums by Login then CallID
to get:
DIF             Callid                          login
2      103092194       2189
1      103095424                      2189
4      103096980                      2192
3      103097978                      2193
2      103097581       2193

but not able to count and then add another colum with count interval and order from most to least
0
TechMonster
Asked:
TechMonster
  • 3
  • 3
1 Solution
 
dqmqCommented:

Select  CallID, LoginID, count(*) from yourtable
group by callid, loginID
order by count(*) desc
0
 
TechMonsterAuthor Commented:
select callid, login, count(*)

from (select datediff(s, min(timestamp),max(timestamp)) as Diff, callid, login

           from  dbo.CallStat

          where login not in ('', '5874','5873')

          group by login, callid
                  where diff<30
                  
order by count(login) DESC...
GET error"Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'where'.
"...

Am I supposed to do another query from the above result table?  
i thought you could run 1 query for the final result.

thanks
0
 
dqmqCommented:
select callid, login, count(*)
from
   (select datediff(s, min(timestamp),max(timestamp)) as Diff, callid, login
           from  dbo.CallStat
          where login not in ('', '5874','5873')
    )
where diff<30
group by login, callid
order by count(*)  desc
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dqmqCommented:
Disregard, previous post--while syntactically correct, it will compute diff across all rows irrespective of the grouping. I think this is more what you want:

select datediff(s, min(timestamp),max(timestamp)) as Diff, callid, login, count(*)
from dbo.CallStat
where login not in ('', '5874','5873')
group by callid, login
having  diff<30
order by count(*)  desc
0
 
OmnibuzzCommented:
can you paste the expected output?
0
 
TechMonsterAuthor Commented:
Expected output should be just
login and number of times they show up on a row.
DIF             Callid                          login
2     103092194                     2189
1     103095424                     2189
4     103096980                     2192
3     103097978                     2193
2     103097581                     2193

Would produce:

login            amount
2189              2
2193              2
2189              1
0
 
TechMonsterAuthor Commented:
dqmq code actually does count all the columns.  by group but I was looking for
something within my code. There was more to the code which i was not allowed  to disclose.

i give dqmq the points but have to only give a "B" for a grade.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now