Destiny Amana
asked on
Group By Command on multiple tables
Hello,
I have a couple of MSSQL tables scripted below for your use in this help.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_gic _partner_u niversitie s]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_gic_partner_uni versities]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_gic _applicant _sponsorsh ip]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_gic_applicant_s ponsorship ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_gic _applicant s]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_gic_applicants]
GO
CREATE TABLE [dbo].[tbl_gic_partner_uni versities] (
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[uniname] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[logo] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[writeup] [ntext] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[status] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[webaddress] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[email] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[upsize_ts] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_gic_applicant_s ponsorship ] (
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[applicantid] [int] NOT NULL ,
[sponsor] [ntext] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[sponsoroccupation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[affordinitialfees] [bit] NOT NULL ,
[unichoice1] [int] NULL ,
[unichoice2] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_gic_applicants] (
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[gicnumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[firstname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[lastname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[mobile] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[telhome] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[telwork] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[gender] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[dateofbirth] [datetime] NULL ,
[dateofregistration] [datetime] NULL ,
[status] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[nationality] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[address] [ntext] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[referall] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[applicationstatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[visa] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[paidfee] [bit] NULL ,
[offerletter] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[countryofapplication] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[source] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I want to display a list of Universities by name and the number of applicants that have appied to that university either as 1st choice or second choice.
Originally, I had
"SELECT COUNT(*) AS numberapplied, unichoice1 "&_
"FROM tbl_gic_applicant_sponsors hip "&_
"GROUP BY unichoice1 "
for just grouping first university choice but it does not really give an accurate count.
Can anyoe help me here?
I have a couple of MSSQL tables scripted below for your use in this help.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_gic
drop table [dbo].[tbl_gic_partner_uni
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_gic
drop table [dbo].[tbl_gic_applicant_s
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_gic
drop table [dbo].[tbl_gic_applicants]
GO
CREATE TABLE [dbo].[tbl_gic_partner_uni
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[uniname] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_
[logo] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_
[writeup] [ntext] COLLATE SQL_Latin1_General_CP1_CI_
[status] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_
[webaddress] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_
[email] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_
[upsize_ts] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_gic_applicant_s
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[applicantid] [int] NOT NULL ,
[sponsor] [ntext] COLLATE SQL_Latin1_General_CP1_CI_
[sponsoroccupation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[affordinitialfees] [bit] NOT NULL ,
[unichoice1] [int] NULL ,
[unichoice2] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_gic_applicants]
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[gicnumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[firstname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[lastname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[mobile] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[telhome] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[telwork] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[gender] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_
[dateofbirth] [datetime] NULL ,
[dateofregistration] [datetime] NULL ,
[status] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_
[nationality] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[address] [ntext] COLLATE SQL_Latin1_General_CP1_CI_
[city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[referall] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_
[applicationstatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[visa] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[paidfee] [bit] NULL ,
[offerletter] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_
[countryofapplication] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[source] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I want to display a list of Universities by name and the number of applicants that have appied to that university either as 1st choice or second choice.
Originally, I had
"SELECT COUNT(*) AS numberapplied, unichoice1 "&_
"FROM tbl_gic_applicant_sponsors
"GROUP BY unichoice1 "
for just grouping first university choice but it does not really give an accurate count.
Can anyoe help me here?
Also between tbl_gic_applicant_sponsors hip and tbl_gic_partner_universiti es
Regards,
dduser
Regards,
dduser
Try this
SELECT T.uniname, COUNT(A.*)+ COUNT(B.*) AS numberapplied, COUNT(A.*) as unichoice1 ,COUNT(B.*) as unichoice1
FROM tbl_gic_partner_universiti es as T
INNER JOIN tbl_gic_applicant_sponsors hip as A ON A.unichoice1=T.autoid
INNER JOIN tbl_gic_applicant_sponsors hip as B ON B.unichoice2=T.autoid
GROUP BY T.uniname
SELECT T.uniname, COUNT(A.*)+ COUNT(B.*) AS numberapplied, COUNT(A.*) as unichoice1 ,COUNT(B.*) as unichoice1
FROM tbl_gic_partner_universiti
INNER JOIN tbl_gic_applicant_sponsors
INNER JOIN tbl_gic_applicant_sponsors
GROUP BY T.uniname
Select uniname,Choice1.TotalCount as FirstChoice,Choice2.TotalC ount as SecondChoice from tbl_gic_partner_universiti es as University left outer join
(Select unichoice1,count(*) as TotalCount from tbl_gic_applicant_sponsors hip where unichoice1 is not null group by unichoice1) as Choice1 on Choice1.unichoice1 = University.autoid left outer join
(Select unichoice2,count(*) as TotalCount from tbl_gic_applicant_sponsors hip where unichoice2 is not null group by unichoice2) as Choice2 on Choice2.unichoice2 = University.autoid
Considering autoid from tbl_gic_partner_universiti es is equal to unichoice1 or unichoice2
This should work.
Regards,
dduser
(Select unichoice1,count(*) as TotalCount from tbl_gic_applicant_sponsors
(Select unichoice2,count(*) as TotalCount from tbl_gic_applicant_sponsors
Considering autoid from tbl_gic_partner_universiti
This should work.
Regards,
dduser
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yogeshup
YOU ARE DA BOMB!
Thanks guys
YOU ARE DA BOMB!
Thanks guys
Regards,
dduser