Solved

How to write the group by query to find highest.

Posted on 2013-05-18
7
271 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 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