Grouping based on two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping based on two tables

Author

Topic

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-02 : 03:53:08

Hi,
I have two tables:

Table 1:
Meeting : (Id, MeetingId, PeriodId, Description)
MeetingId is the Primary key.

Table 2:
Decision : (ID, DecisionId, MeetingId)
DecisionId is the primary key, MeetingId refers the Meeting table.
-----------

My query:

select A.PeriodId, A.MeetingCount, COUNT(decision.MeetingId)
from decision
inner join
(Select PeriodId, COUNT(PeriodId) as MeetingCount, MeetingId
from meeting
group by PeriodId,MeetingId) A
on (Decision.meetingid = A.MeetingId)
group by A.periodid, A.MeetingCount, decision.MeetingId

My output for the above query

PeriodId Meetings Decisions
-------- ----------- ------------
PE001 1 3
PE001 1 2
PE001 1 4
PE002 1 2
PE002 1 3

But the required output is

PeriodId Meetings Decisions
-------- ----------- ------------
PE001 3 9
PE002 2 5

-------------------------------

Thanks for your help.

Rgds
SSM.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-06-02 : 04:07:24

quote:
Originally posted by sureshmanian

select A.PeriodId, SUM(A.MeetingCount), COUNT(decision.MeetingId)
from decision
inner join
(Select PeriodId, COUNT(PeriodId) as MeetingCount, MeetingId
from meeting
group by PeriodId,MeetingId) A
on (Decision.meetingid = A.MeetingId)
group by A.periodid~~, A.MeetingCount, decision.MeetingId~~

--------------------------
http://connectsql.blogspot.com/

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-02 : 04:20:00

Hi lionofdezert,
According to your suggestion I got same output on both columns

PeriodId Meetings Decisions
-------- ----------- ------------
PE001 9 9
PE002 5 5

It is NOT the expected output.
------------

Rgds
SSM

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-06-02 : 04:28:54

can you give your
Table DDL
,Sample data and
Expected output

--Ranjit

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-06-02 : 04:39:55

SELECT PeriodId,COUNT(DISTINCT Meeting.MeetingId),COUNT (Decision.MeetingId)
FROM Meeting
INNER JOIN Decision ON Meeting.MeetingId = Decision.MeetingId
GROUP BY PeriodId

--------------------------
http://connectsql.blogspot.com/

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-02 : 04:42:06

Hi Ranjit,
Please find the ddl, records and expected output. Thanks -- SSM.
-----------

-- Meeting Table

CREATE TABLE [dbo].[Meeting](
[Id] [int] NOT NULL,
[meetingId] [nvarchar](20) NOT NULL,
[periodid] [nvarchar](20) NOT NULL,
[description] [nvarchar](20) NOT NULL,
CONSTRAINT [pk_meeting] PRIMARY KEY CLUSTERED
(
[meetingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-- Decision Table

CREATE TABLE [dbo].[decision](
[id] [int] NULL,
[decisionid] [nvarchar](20) NOT NULL,
[meetingid] [nvarchar](20) NOT NULL,
CONSTRAINT [pk_decision] PRIMARY KEY CLUSTERED
(
[decisionid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[decision] WITH CHECK ADD CONSTRAINT [FK_decision_Meeting] FOREIGN KEY([meetingid])
REFERENCES [dbo].[Meeting] ([meetingId])
GO

ALTER TABLE [dbo].[decision] CHECK CONSTRAINT [FK_decision_Meeting]
GO

--------------------

-- Insertion in Meeting

insert into Meeting values (1,'M001', 'PE001', 'Period 1 First Meeting')
insert into Meeting values (2,'M002', 'PE001', 'Period 1 Second Meeting')
insert into Meeting values (3,'M003', 'PE001', 'Period 1 Third Meeting')

insert into Meeting values (4,'M004', 'PE002', 'Period 2 First Meeting')
insert into Meeting values (5,'M005', 'PE002', 'Period 2 Second Meeting')

----
-- Insertion in Decision

insert into decision values( 1,'D001', 'M001');
insert into decision values( 2,'D002', 'M001');
insert into decision values( 3,'D003', 'M001');

insert into decision values( 4,'D004', 'M002');
insert into decision values( 5,'D005', 'M002');
insert into decision values( 6,'D006', 'M002');
insert into decision values( 7,'D007', 'M002');

insert into decision values( 8,'D008', 'M003');
insert into decision values( 9,'D009', 'M003');

insert into decision values(10, 'D0010', 'M004');
insert into decision values(11, 'D0011', 'M004');
insert into decision values(12, 'D0012', 'M004');
insert into decision values(13, 'D0013', 'M004');
insert into decision values(14, 'D0014', 'M005');

-- Expected Output

--Period Meetings Decisions
----------------------------
PE001 3 9
PE002 2 5

------------

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-06-02 : 04:58:11

[code]
select
periodid as Period
,count(distinct m.meetingid) as Meetings
,count(decisionid ) as Decisions
from Meeting m
inner join decision d on d.[meetingid]=m.[meetingid]
group by periodid
[/code]

--Ranjit

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-02 : 05:10:54

Hi lionofdezert,

SELECT PeriodId,COUNT(DISTINCT Meeting.MeetingId),COUNT (Decision.MeetingId)
FROM Meeting
INNER JOIN Decision ON Meeting.MeetingId = Decision.MeetingId
GROUP BY PeriodId
---------
This query is working fine when MeetingId(Meeting table) has child row in Decision table. There is the situation, meeting might be conducted but there is no decisions have arrived. In that case your query shows less value for the meetings.

Period Meetings Decisions
------ --------- --------
PE001 3 9
PE002 1 (2) 5
-----------------------
In the row2, there should be two i marked in brackets (2), but it shows as 1.
-------------------
I hope I have made clear. Thanks.

Rgds
SSM

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-06-02 : 05:17:41

Use Left Outer Join

SELECT PeriodId,COUNT(DISTINCT Meeting.MeetingId),COUNT (Decision.MeetingId)
FROM Meeting
LEFT OUTER JOIN Decision ON Meeting.MeetingId = Decision.MeetingId
GROUP BY PeriodId

--------------------------
http://connectsql.blogspot.com/

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-02 : 05:17:53

Hi Ranjit,
Your query as well didn't given me the correct output. In my previous reply to 'lionofdezert', i have explained about the issue. Please check.

Rgds
SSM

sureshmanian
Starting Member

26 Posts

Posted - 2011-06-02 : 05:30:13

Hi lionofdezert,

Its working fine.

select
periodid as Period
,count(distinct m.meetingid) as Meetings
,count(decisionid ) as Decisions
from Meeting m
left outer join decision d on d.[meetingid]=m.[meetingid]
group by periodid

Thanks for your help.
-----------------
Thanks Mr. Ranjit as well.

-----------

Rgds
SSM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources