April 24, 2013 at 9:50 pm
Hi friends i have small doubt in sql server ,
how to write an sql query for getting employee names for the employees whose count is greater than 3 in a city?
table data contains like
idnamelocationdeptno
1ahyd10
2bhyd20
3shyd10
4cbang10
5hbang10
6jachen20
7kachen30
8klhyd50
9kledhyd20
10fedhyd40
11lmbang40
12lgebang30
actualy i try query like this
select location,COUNT(name) from employees group by location having count(*)>3
that time its display
location(count)
bang4
hyd6
but i want only display names details
how to solve this issuse.plese tell me the query
April 24, 2013 at 9:55 pm
To me, it sounds like you want it be name and location. If so, it would look like this:
select name, location,COUNT(*)
from employees
group by name, location
having count(*)>3
If you only want to show it by name, but still where location is greater than three, just get rid of location from the select statement and leave it in the group by:
select name, COUNT(*)
from employees
group by name, location
having count(*)>3
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
April 24, 2013 at 10:48 pm
IN THIS QUERES OUT PUT RESULT NOT COME .
April 24, 2013 at 10:50 pm
Please explain again what exactly you need. I don't completely understand
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
April 24, 2013 at 11:26 pm
Hi asranantha,
you may be get result by using any one of the below query.
#Query 1
;with cte(Location)
as
(
select Location from Test group by Location having COUNT(*)>3
)
select * from Test t inner join cte c on t.Location=c.Location
#End Query 1
#Query 2
select * from Test t inner join ( select Location from Test group by Location having COUNT(*)>3) c on t.Location=c.Location
#End Query 2
--chalam
April 25, 2013 at 12:02 am
Hi,
we are not clear with your requirements..
can you plz give us more details?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2013 at 8:21 am
Please take a few minutes and read the first article in my signature. We need ddl and sample data. Keep in mind we can't see your screen, we don't know your data structures and we aren't familiar with your applications.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2013 at 9:03 am
Since you haven't showed us what you want, it is hard to provide you with an answer. To help I have created the SQL needed to allow others to help once you have figured out and shown us what it is you want based on the sample data you provided:
declare @test-2 table(
TestId int,
TestName varchar(10),
Location varchar(10),
DeptNo int
);
insert into @test-2
values
(1, 'a', 'hyd', 10),
(2, 'b', 'hyd', 20),
(3, 's', 'hyd', 10),
(4, 'c', 'bang', 10),
(5, 'h', 'bang', 10),
(6, 'ja', 'chen', 20),
(7, 'ka', 'chen', 30),
(8, 'kl', 'hyd', 50),
(9, 'kled', 'hyd', 20),
(10, 'fed', 'hyd', 40),
(11, 'lm', 'bang', 40),
(12, 'lge', 'bang', 30);
select * from @test-2;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply