February 17, 2010 at 3:45 am
Hi,
I have used the following query to get the result that is shown below. I have also attached the table schema.
Prospect_lists
--------------
CREATE TABLE [dbo].[prospect_lists](
[id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date_entered] [datetime] NULL,
[date_modified] [datetime] NULL,
[modified_user_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[assigned_user_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_by] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[deleted] [bit] NOT NULL,
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Prospect_lists_prospects
------------------------
CREATE TABLE [dbo].[prospect_lists_prospects](
[id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[prospect_list_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[related_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[related_type] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date_modified] [datetime] NULL,
[deleted] [bit] NULL,
CONSTRAINT [uk_prospect_lists_prospects] UNIQUE NONCLUSTERED
(
[prospect_list_id] ASC,
[related_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Query:
select PL.id as id, PL.name as Name,count(PLP.related_id) as NoofContacts from prospect_lists PL, prospect_lists_prospects PLP Where PL.id = PLP.prospect_list_id Group By PL.id, PL.name
Result
-----
ID Name NoofContacts
-- ---- ------------
d79fc6bdd9a0 01/02/2009 3
a641ec52b352 01092009 8
e07e9befe726 01162009 4
4a24d2e2bd75 01302009 8
f60ea6a2bf1b 02/06/2009 26
77905c379bd6 02/13/2009 32
ac810f28c3f0 02/20/2009 40
3f6268356f24 02062009 6
28a8864c820d 02132009 7
43434cc6967d 02202009 9
1c31ef79f1ef 03/06/2009 27
d2c725b4e6de 03/06/2009 8
c3fe897419c4 03/13/2009 9
24b02a823136 03/20/2009 8
c58677e1bc95 03/20/2009 23
e6105d1a5531 03/27/2009 22
13d1480772a6 03/27/2009 6
f7a5b9a8f8c9 03132009 21
7552f67088f8 04/03/2009 8
c96e21618eb4 04/03/2009 18
754c3e3a3a3d 04/10/2009 18
df05553bc251 04/10/2009 8
The requirement that i need is that i want to display how many releated_id is present for a particular Prospect_list_id along with the name. I want to acheive the above result without using "Group By" clause. Till now i have not been working in SQL queries, this is my first attempt at SQL querires. Can someone please help me out?
Thanks,
Raja.
February 17, 2010 at 8:20 am
rajagopalanseeth (2/17/2010)
The requirement that i need is that i want to display how many releated_id is present for a particular Prospect_list_id along with the name. I want to acheive the above result without using "Group By" clause. Till now i have not been working in SQL queries, this is my first attempt at SQL querires. Can someone please help me out?
First, I want to thank you for providing the table structure creation code... it really does help us see what all is going on. What would be even better would be to also supply the test data, in the form on INSERT statements, so that we can just cut-and-paste into SSMS to more quickly help you out. And also what the expected result would be based on the sample data. See the first link in my signature for how to do this.
Now, I have a question... it sounds like you want to query for just ONE prospect_list_id. To do that, you would run:
select count(*) from dbo.prospect_list_prospects where prospect_list_id = x
where x would be the id you are quering.
Now, if what you are looking for is a count for all prospect_list_id's (which if we did have sample data and expected results, we would have been able to figure that out), then you will have to use the GROUP BY. Why would you want to avoid that?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 17, 2010 at 9:32 am
Hi,
The actual reason is that i will display the id, name and noofcontacts in a grid. I want to display all the prospect_list_id along with the name, noofcontacts and description, that is present in the table "Prospect_lists" By default it will show only 15 rows in the grid, when i click on "next" in the grid, it's showing some values that has already been showed. The reason is "Group By", bcos of that some of the data's are repeated in the next page. I want to avoid that. The only way to avoid is not to use Group By clause.
Thanks,
Raja.
February 17, 2010 at 9:44 am
I can't seem to think of a way to do this without the GROUP BY clause, either. I suppose you could build something to do it, but I think you'd be losing quite a bit of performance to get it done. Possibly some way to loop through each record, not what you would want to do.
Here's what I used:
I just saw your reply, is the row_number() select statement at the bottom closer to what you are thinking? I didn't follow how using GROUP BY gave you duplicate records. Is there a solution by changing some setting in the grid, perhaps? What are you using(software) to create the grid?
if object_id('tempdb..#prospect_lists') is not null drop table #prospect_lists
if object_id('tempdb..#prospect_lists_prospects') is not null drop table #prospect_lists_prospects
--Prospect_lists
--------------
CREATE TABLE #prospect_lists(
[id] [varchar](36) NOT NULL,
[name] [varchar](50) NULL,
[date_entered] [datetime] NULL,
[date_modified] [datetime] NULL,
[modified_user_id] [varchar](36) NULL,
[assigned_user_id] [varchar](36) NULL,
[created_by] [varchar](36) NULL,
[deleted] [bit] NOT NULL,
[description] [text] NULL,
)
--Prospect_lists_prospects
------------------------
CREATE TABLE #prospect_lists_prospects(
[id] [varchar](36) NOT NULL,
[prospect_list_id] [varchar](36) NULL,
[related_id] [varchar](36) NULL,
[related_type] [varchar](25) NULL,
[date_modified] [datetime] NULL,
[deleted] [bit] NOT NULL
)
insert into #prospect_lists values ('1','John',null,null,null,null,null,0,null)
insert into #prospect_lists values ('2','Michael',null,null,null,null,null,0,null)
insert into #prospect_lists values ('3','Steve',null,null,null,null,null,0,null)
insert into #prospect_lists values ('4','Gabe',null,null,null,null,null,0,null)
insert into #prospect_lists values ('5','Henry',null,null,null,null,null,0,null)
insert into #prospect_lists values ('6','Luke',null,null,null,null,null,0,null)
insert into #prospect_lists values ('7','Paul',null,null,null,null,null,0,null)
insert into #prospect_lists values ('8','William',null,null,null,null,null,0,null)
insert into #prospect_lists values ('9','Bob',null,null,null,null,null,0,null)
insert into #prospect_lists values ('10','Adam',null,null,null,null,null,0,null)
insert into #prospect_lists_prospects values('21','1','A',null,null,0)
insert into #prospect_lists_prospects values('22','2','A',null,null,0)
insert into #prospect_lists_prospects values('23','3','A',null,null,0)
insert into #prospect_lists_prospects values('24','4','A',null,null,0)
insert into #prospect_lists_prospects values('25','1','B',null,null,0)
insert into #prospect_lists_prospects values('26','1','C',null,null,0)
insert into #prospect_lists_prospects values('27','7','C',null,null,0)
insert into #prospect_lists_prospects values('28','8','C',null,null,0)
insert into #prospect_lists_prospects values('29','9','D',null,null,0)
insert into #prospect_lists_prospects values('30','9','E',null,null,0)
select PL.id as id, PL.name as Name,count(PLP.related_id) as NoofContacts
from #prospect_lists PL inner join #prospect_lists_prospects PLP on PL.id = PLP.prospect_list_id
Group By PL.id, PL.name
select PL.id as id, PL.name as Name,row_number() over (partition by PLP.related_id order by pl.name) as NoofContacts
from #prospect_lists PL inner join #prospect_lists_prospects PLP on PL.id = PLP.prospect_list_id
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply