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
e07e9befe72601162009 4
4a24d2e2bd7501302009 8
f60ea6a2bf1b02/06/2009 26
77905c379bd602/13/2009 32
ac810f28c3f002/20/2009 40
3f6268356f2402062009 6
28a8864c820d02132009 7
43434cc6967d02202009 9
1c31ef79f1ef03/06/2009 27
d2c725b4e6de03/06/2009 8
c3fe897419c403/13/2009 9
24b02a82313603/20/2009 8
c58677e1bc9503/20/2009 23
e6105d1a553103/27/2009 22
13d1480772a603/27/2009 6
f7a5b9a8f8c903132009 21
7552f67088f804/03/2009 8
c96e21618eb404/03/2009 18
754c3e3a3a3d04/10/2009 18
df05553bc25104/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