Solved

How to write the group by query to find highest.

Posted on 2013-05-18
7
272 Views
Last Modified: 2013-06-07
I have a table mstenquiries
It contains city,course,feeddate
i want to find the feeddate having highest no. of enquiries.

select top 1 Convert(varchar,feeddate,106),count(*)  from cuaf.dbo.mstcuaf where sname not like 'Test%' and course<>'' group by  Convert(varchar,feeddate,106) order by 2 desc

This result will give date and highest count
Now i want the course on this day which was having the highest enquiries.

How to find that/
0
Comment
Question by:searchsanjaysharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 84 total points
ID: 39177529
try the below code

;with cte as
(
select top 1 Convert(varchar,feeddate,106) feeddate,count(1)  co
from cuaf.dbo.mstcuaf 
where sname not like 'Test%' 
and course<>'' 
group by  Convert(varchar,feeddate,106) 
order by 2 desc
)
select top 1 course,count(1)
from cuaf.dbo.mstcuaf m
join cte c
on c.feeddate = Convert(varchar,m.feeddate,106)
where m.sname not like 'Test%' 
and m.course<>'' 
group by course
order by 2 desc

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 39177876
So is your table called mstenquiries or cuaf.dbo.mstcuaf ?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 39177882
Assuming it is cuaf.dbo.mstcuaf then something like this should do it:
SELECT TOP 1 course, COUNT(*) EnquiriesByCourse
FROM cuaf.dbo.mstcuaf m
	INNER JOIN (
			SELECT	TOP 1
					feeddate,
					COUNT(*) EnquiriesByDate
			FROM    cuaf.dbo.mstcuaf
			WHERE   sname NOT LIKE 'Test%'
					AND course <> ''
			GROUP BY feeddate
			ORDER BY EnquiriesByDate DESC) x ON m.feeddate = x.feeddate
GROUP BY
		EnquiriesByCourse

Open in new window

0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 12

Assisted Solution

by:sachitjain
sachitjain earned 83 total points
ID: 39178095
There could be a generic query to find Nth highest and in this case it is:
;with cte (feedate, countOfEnquiries) as
(
      select Convert(varchar,feeddate,106), count(course) from cuaf.dbo.mstcuaf group by Convert(varchar,feeddate,106)
)
select x.feedate, x.countOfEnquiries
from cte x
where {N-1} = (select count(*)
                  from cte y
                  where y.countOfEnquiries > x.countOfEnquiries)


So in this case, since you are going to find highest value so your N in {N-1} becomes 1 and query becomes
;with cte (feedate, countOfEnquiries) as
(
      select Convert(varchar,feeddate,106), count(course) from cuaf.dbo.mstcuaf group by Convert(varchar,feeddate,106)
)
select x.feedate, x.countOfEnquiries
from cte x
where 0 = (select count(*)
                  from cte y
                  where y.countOfEnquiries > x.countOfEnquiries)


In case you want to find second highest, your N becomes 2, 3rd highest your N becomes 3 and so on.
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 167 total points
ID: 39178297
I think aceprkins has wrong field in GROUP BY clause.  And also the ORDER BY is missing.
SELECT TOP 1 course, COUNT(*) EnquiriesByCourse
FROM cuaf.dbo.mstcuaf m
	INNER JOIN (
			SELECT	TOP 1
					feeddate,
					COUNT(*) EnquiriesByDate
			FROM    cuaf.dbo.mstcuaf
			WHERE   sname NOT LIKE 'Test%'
					AND course <> ''
			GROUP BY feeddate
			ORDER BY EnquiriesByDate DESC) x ON m.feeddate = x.feeddate
GROUP BY
		course
ORDER BY EnquiriesByCourse DESC

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 167 total points
ID: 39178639
you can also try like this.
;WITH CTE 
     AS (SELECT *, 
                COUNT(*) 
                  OVER ( 
                    PARTITION BY CONVERT(VARCHAR, feeddate, 106))         feddate_cnt, 
                COUNT(*) 
                  OVER ( 
                    PARTITION BY CONVERT(VARCHAR, feeddate, 106), course) course_cnt 
           FROM CUAF.DBO.mstcuaf 
          WHERE sname NOT LIKE 'Test%' 
            AND course <> '') 
SELECT TOP 1 city, 
             course, 
             feeddate 
  FROM CTE 
 ORDER BY feddate_cnt DESC, 
          course_cnt DESC 

Open in new window

0
 

Author Closing Comment

by:searchsanjaysharma
ID: 39229036
tx
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question