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?
LVL 1
souldjAsked:
Who is Participating?
 
YogeshupConnect With a Mentor Commented:
This seems much easier. Not sure If I am missing something

SELECT   T.uniname,  sum( case when A.unichoice1 = T.autoid then 1 else 0 end) choice1,
sum( case when A.unichoice2 = T.autoid then 1 else 0 end) choice2
FROM     tbl_gic_partner_universities  as T
INNER JOIN tbl_gic_applicant_sponsorship as A
ON A.unichoice1=T.autoid or A.unichoice2=T.autoid
group by T.uniname
0
 
dduserCommented:
What is the relation between tables:- tbl_gic_applicant_sponsorship & tbl_gic_applicants??

Regards,

dduser
0
 
dduserCommented:
Also between tbl_gic_applicant_sponsorship and tbl_gic_partner_universities

Regards,
dduser
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
HuyBDCommented:
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
0
 
dduserCommented:
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
0
 
souldjAuthor Commented:
Yogeshup


YOU ARE DA BOMB!

Thanks guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.