Solved

Group By Command on multiple tables

Posted on 2006-11-15
6
629 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:souldj
6 Comments
 
LVL 9

Expert Comment

by:dduser
ID: 17945235
What is the relation between tables:- tbl_gic_applicant_sponsorship & tbl_gic_applicants??

Regards,

dduser
0
 
LVL 9

Expert Comment

by:dduser
ID: 17945249
Also between tbl_gic_applicant_sponsorship and tbl_gic_partner_universities

Regards,
dduser
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 17945256
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 9

Expert Comment

by:dduser
ID: 17945263
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
 
LVL 1

Accepted Solution

by:
Yogeshup earned 400 total points
ID: 17945680
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
 
LVL 1

Author Comment

by:souldj
ID: 17945995
Yogeshup


YOU ARE DA BOMB!

Thanks guys
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question