Filter derivied COUNT result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Filter derivied COUNT result

Author

Topic

perwyl
Starting Member

5 Posts

Posted - 2009-07-11 : 14:01:01

Hi,

I am stuck with this SQL. I am suppose to find the most popular facility that the member booked.

SELECT FName, COUNT(*) AS "TotalNumber"
FROM Book
INNER JOIN Facility
ON Book.Facilitycode = Facility.Facilitycode
GROUP BY Facility.FacilityCode, FName

I used table: Book, Facility

The result i get is that the system counts out how many times facility is being booked by the member. But i only want the most popular(highest count) facility item to be displayed.

I tried using MAX(COUNT(*)), But got error msg : Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

May i know how should i go about solving this? Tks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-11 : 18:23:17

Could not get that.
Can you please give sample data and wanted output?

No, you're never too old to Yak'n'Roll if you're too young to die.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-11 : 18:48:27

Use MAX(TotalNumber).

perwyl
Starting Member

5 Posts

Posted - 2009-07-11 : 23:46:43

Sample Data:

CREATE TABLE Facility
(
FacilityCode varchar(5) not null,
FName varchar(20) not null,
FDescription varchar(200) null,
FLocation varchar(60) not null,
Rate smallmoney null,

CONSTRAINT PK_Facility PRIMARY KEY(FacilityCode)

)
Go

CREATE TABLE Book
(
BookingID varchar(10),
BDateTime datetime null,
BDuration int null,
MemberID varchar(10) null,
FacilityCode varchar(5) null,

CONSTRAINT PK_Book PRIMARY KEY(BookingID),
CONSTRAINT FK_Book_MemberID FOREIGN KEY (MemberID)
REFERENCES Member(MemberID),
CONSTRAINT FK_Book_FacilityCode FOREIGN KEY(FacilityCode)
REFERENCES Facility(FacilityCode)

)
Go

FacilityCode: Fn1, Fn2, Gym1, Gym2

FName: Function Room 1, Function Room 2, Gymnasium 1, Gymnasium 2

MemberID: M1, M2, M3, M4

INSERT INTO Book VALUES('B1','15-Aug-2009 14:00', 2, 'M1','Gym1')
INSERT INTO Book VALUES('B2','05-Jul-2009 08:00', 1, 'M2','Gym2')
INSERT INTO Book VALUES('B3','20-Jun-2009 14:00', 2, 'M3','Gym1')
INSERT INTO Book VALUES('B4','12-May-2009 14:00', 5, 'M4','Fn2')
-------------------------------------------------------------------

Wanted output is Gymnasium 3

I want to ommit the last number of the FName, COUNT and display the total number of the facilityCode that is being booked by the Member.

perwyl
Starting Member

5 Posts

Posted - 2009-07-12 : 01:15:26

OKEY! I MANAGE TO SOLVE IT~~~YEAH~~

SELECT MAX(FacilityName) AS "Facility Name", MAX(TotalNumber)AS "Total Number"
FROM
(SELECT LEFT(FName, LEN(FName)-1) AS "FacilityName", COUNT(FName) AS "TotalNumber" 
FROM Book
	INNER JOIN Facility
		ON Book.FacilityCode = Facility.FacilityCode
WHERE YEAR(BDateTime) = 2009
GROUP BY Book.FacilityCode, FName)x

This will only display the highest facility booked by the member in year 2009 :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 03:28:21

quote:
Originally posted by perwyl

OKEY! I MANAGE TO SOLVE IT~~~YEAH~~

SELECT MAX(FacilityName) AS "Facility Name", MAX(TotalNumber)AS "Total Number"
FROM
(SELECT LEFT(FName, LEN(FName)-1) AS "FacilityName", COUNT(FName) AS "TotalNumber"
FROM Book
INNER JOIN Facility
ON Book.FacilityCode = Facility.FacilityCode
WHERE YEAR(BDateTime) = 2009
GROUP BY Book.FacilityCode, FName)x

This will only display the highest facility booked by the member in year 2009 :)

you cant guarantee that this will return the Facility Name and total number of same record. may be what you need is this


SELECT TOP 1 LEFT(FName, LEN(FName)-1) AS "FacilityName", COUNT(FName) AS "TotalNumber" 
FROM Book
	INNER JOIN Facility
		ON Book.FacilityCode = Facility.FacilityCode
WHERE YEAR(BDateTime) = 2009
GROUP BY Book.FacilityCode, FName
ORDER BY TotalNumber DESC

perwyl
Starting Member

5 Posts

Posted - 2009-07-12 : 05:00:28

I've modified the coding...

SELECT TOP 1 FacilityName, SUM(TotalNumber) AS "BookedTimes"
FROM
(SELECT LEFT(FName, LEN(FName)-1) AS "FacilityName", COUNT(FName) AS "TotalNumber" 
FROM Book
	INNER JOIN Facility
		ON Book.FacilityCode = Facility.FacilityCode
WHERE YEAR(BDate) = 2009
GROUP BY Book.FacilityCode, FName)x
GROUP By FacilityName
ORDER BY BookedTimes desc

This will sure give me the correct answer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 05:10:08

quote:

Originally posted by perwyl

I've modified the coding...

SELECT TOP 1 FacilityName, SUM(TotalNumber) AS "BookedTimes"
FROM
(SELECT LEFT(FName, LEN(FName)-1) AS "FacilityName", COUNT(FName) AS "TotalNumber" 
FROM Book
	INNER JOIN Facility
		ON Book.FacilityCode = Facility.FacilityCode
WHERE YEAR(BDate) = 2009
GROUP BY Book.FacilityCode, FName)x
GROUP By FacilityName
ORDER BY BookedTimes desc

This will sure give me the correct answer.

whats the need of outer group by?

perwyl
Starting Member

5 Posts

Posted - 2009-07-13 : 02:14:54

Cox i have for example, tennis court 1 (booked once), tennis court 2 (booked twice).

I need to count them together and display as: tennis court (booked 3 times)

Without the outer layer, it will only display: tennis court (booked twice)

------------------------

Thanks for helping

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 13:34:06

then why dont you group by field in first time itself? why use facilitycode?

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 06:26:36

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources