[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Group By Command on multiple tables

Posted on 2006-11-15
6
Medium Priority
?
635 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1600 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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