September 23, 2009 at 12:33 pm
I want to query my table and I want to return data as well as count of the types such as this.
select name, type, hiredate from tblEmp
and it returns something like this
smith Salary 8/8/95
Johnson Hourly 9/9/09
Jones Salary 7/7/85
Stewart Houlry 7/5/96
and I would like to see this as well
Salary 2
Hourly 2
is there anyway to do this kind of output in one query, or should I just do seperate queries or just have the application do the counting of the types?
September 23, 2009 at 1:02 pm
I am sure there are lot of other elegant ways to do this and people would suggest. But see if this helps. (BTW please give the test data like this).
Not even sure if this is what you were looking for ( as it looks ugly this way)
Create table tblEmp(name varchar(10), sType varchar(10),hiredate datetime)
INSERT INTO tblEmp VALUES('smith', 'Salary', '8/8/95')
INSERT INTO tblEmp VALUES('Johnson', 'Hourly', '9/9/09')
INSERT INTO tblEmp VALUES('Jones', 'Salary', '7/7/85')
INSERT INTO tblEmp VALUES('Stewart', 'Hourly', '7/5/96')
Create function counts(@type as varchar(max))
RETURNS int
AS
BEGIN
DECLAre @urCount as int
SELECT @urCount =COUNT(*) from tblEmp where sType = @type
RETURN @urCount
END
Select *, dbo.counts(sType) from tblEmp
Result:
smith Salary 1995-08-08 00:00:00.000 2
Johnson Hourly 2009-09-09 00:00:00.000 2
Jones Salary 1985-07-07 00:00:00.000 2
Stewart Hourly 1996-07-05 00:00:00.000 2
---------------------------------------------------------------------------------
September 23, 2009 at 1:20 pm
If your table is going to be any big, you have a real performance killer there with this function.
Let's say you have 1000 employees:
The function will be called 1000 times (for each employee) and generate a table scan of 1000 rows everytime. That makes it 1 000 000 rows scanned, plus your current query, that has 1000 rows to scan (to get the employees information, adding up to a 1 001 000 rows scan, for a small query of 1000 rows.
Now, imagine if the table is even larger, you will simply kill your server with that type of query.
What you could so is either select the data after wards with a grouping, that would scan the table once for the employees information, and another time for the row counts, resulting in 2 000 scans, which after all, is not that bad.
Hope that helps,
Cheers,
J-F
September 23, 2009 at 1:24 pm
If your requirement is to have the result in only 1 query, you could use this kind of query, to get to your requirements.
CREATE TABLE tblEmp (
name VARCHAR(10),
sType VARCHAR(10),
hiredate DATETIME)
INSERT INTO tblEmp
VALUES ('smith',
'Salary',
'8/8/95')
INSERT INTO tblEmp
VALUES ('Johnson',
'Hourly',
'9/9/09')
INSERT INTO tblEmp
VALUES ('Jones',
'Salary',
'7/7/85')
INSERT INTO tblEmp
VALUES ('Stewart',
'Hourly',
'7/5/96')
SELECT *
FROM tblEmp emp
INNER JOIN (SELECT sType,
count(* ) AS [Cnt]
FROM tblEmp
GROUP BY sType) AS GroupedEmp
ON emp.sType = GroupedEmp.sType
DROP TABLE tblEmp
Hope it helps,
Cheers,
J-F
September 23, 2009 at 1:27 pm
thanks for the replies guys and after some thinking about it, I'm going to put this kind of function in the business logic of the application. The table can get big, right now its only 200 employees, but it can grow and I would rather not put that load on the db. thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply