Link to home
Start Free TrialLog in
Avatar of Destiny Amana
Destiny AmanaFlag for Nigeria

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_universities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_gic_partner_universities]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_gic_applicant_sponsorship]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_gic_applicant_sponsorship]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_gic_applicants]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_gic_applicants]
GO

CREATE TABLE [dbo].[tbl_gic_partner_universities] (
      [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_sponsorship] (
      [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_sponsorship "&_
                           "GROUP BY unichoice1 "

for just grouping first university choice but it does not really give an accurate count.

Can anyoe help me here?
Avatar of dduser
dduser

What is the relation between tables:- tbl_gic_applicant_sponsorship & tbl_gic_applicants??

Regards,

dduser
Also between tbl_gic_applicant_sponsorship and tbl_gic_partner_universities

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_universities  as T
INNER JOIN tbl_gic_applicant_sponsorship as A ON A.unichoice1=T.autoid
INNER JOIN tbl_gic_applicant_sponsorship as B ON B.unichoice2=T.autoid
       GROUP BY T.uniname
Select uniname,Choice1.TotalCount as FirstChoice,Choice2.TotalCount as SecondChoice from tbl_gic_partner_universities as University left outer join
(Select unichoice1,count(*) as TotalCount from tbl_gic_applicant_sponsorship 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_sponsorship where unichoice2 is not null group by unichoice2) as Choice2 on Choice2.unichoice2 = University.autoid

Considering autoid from tbl_gic_partner_universities is equal to unichoice1 or unichoice2

This should work.

Regards,

dduser
ASKER CERTIFIED SOLUTION
Avatar of Yogeshup
Yogeshup

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Destiny Amana

ASKER

Yogeshup


YOU ARE DA BOMB!

Thanks guys