Solved

How to apply group by with self join for complex query.

Posted on 2012-12-21
3
413 Views
Last Modified: 2012-12-21
How to apply the complex self join with group by. The data and sample output is attached.
Data-and-Desired-Output.xlsx
0
Comment
Question by:searchsanjaysharma
3 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38714161
I created a subset of your data:

create table tab1 
(course char(10),
 uid char(10),
 subject char(10),
 im decimal)

insert into tab1 values ('B.Com', '12BCM1002', 'CMT-101', 22);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-102', 24);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-103', 25);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-104', 26);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-105', 28);
insert into tab1 values ('B.Com', '12BCM1002', 'CMT-106', 30);

insert into tab1 values ('B.Com', '12BCM1003', 'CMT-101', 22);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-102', 32);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-103', 42);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-104', 52);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-105', 62);
insert into tab1 values ('B.Com', '12BCM1003', 'CMT-106', 72);

Open in new window


And ran this:

select a.course, a.uid,
 max(case when corder = 1 then im else 0 end) as c101,
 max(case when corder = 2 then im else 0 end) as c102,
 max(case when corder = 3 then im else 0 end) as c103,
 max(case when corder = 4 then im else 0 end) as c104,
 max(case when corder = 5 then im else 0 end) as c105,
 max(case when corder = 6 then im else 0 end) as c106,
 c.total_score
from
(select course, uid, subject, im, row_number() over (partition by uid order by uid, subject) as corder
from tab1) as a
join
(select distinct b.course, b.uid, sum(b.im) over (partition by b.uid) as total_score 
from tab1 b) as c on a.uid = c.uid and a.course = c.course
group by a.course,a.uid, c.total_score

Open in new window


I believe it gets you what you want (although I won't promise that it's the most efficient way of getting it :) )
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38714205
** code changed!


This works also:

CREATE TABLE SomeTable (Course varchar(50), UID varchar(50), SubjectCode varchar(50), IM int)

INSERT INTO SomeTable (Course, UID, SubjectCode, IM) VALUES
('B.Com','12BCM1002','CMT-101',22),
('B.Com','12BCM1002','CMT-102',24),
('B.Com','12BCM1002','CMT-103',26),
('B.Com','12BCM1002','CMT-104',28),
('B.Com','12BCM1002','CMT-105',30),
('B.Com','12BCM1002','CMT-106',32),
('B.Com','12BCM1003','CMT-101',32),
('B.Com','12BCM1003','CMT-102',34),
('B.Com','12BCM1003','CMT-103',36),
('B.Com','12BCM1003','CMT-104',38),
('B.Com','12BCM1003','CMT-105',40),
('B.Com','12BCM1003','CMT-106',42),
('B.Com','12BCM1004','CMT-101',42),
('B.Com','12BCM1004','CMT-102',44),
('B.Com','12BCM1004','CMT-103',46),
('B.Com','12BCM1004','CMT-104',48),
('B.Com','12BCM1004','CMT-105',50),
('B.Com','12BCM1004','CMT-106',52),
('B.Com','12BCM1005','CMT-101',62),
('B.Com','12BCM1005','CMT-102',64),
('B.Com','12BCM1005','CMT-103',66),
('B.Com','12BCM1005','CMT-104',68),
('B.Com','12BCM1005','CMT-105',70),
('B.Com','12BCM1005','CMT-106',72),
('B.Com','12BCM1006','CMT-101',72),
('B.Com','12BCM1006','CMT-102',74),
('B.Com','12BCM1006','CMT-103',76),
('B.Com','12BCM1006','CMT-104',78),
('B.Com','12BCM1006','CMT-105',80),
('B.Com','12BCM1006','CMT-106',82)

DECLARE @sql varchar(max)

SET @sql= 'SELECT s1.Course, 
    s1.UID'

SELECT @sql = @sql + ', 
    (SELECT SUM(s2.IM) 
    FROM SomeTable s2 
    WHERE s2.Course = s1.Course AND s2.UID = s1.UID AND s2.SubjectCode = ''' + SubjectCode + ''') AS [' + SubjectCode + ']'
FROM SomeTable
GROUP BY SubjectCode
ORDER BY SubjectCode

SET @sql = @sql + ', 
    SUM(s1.IM) AS Total 
FROM SomeTable s1 
GROUP BY s1.Course, s1.UID 
ORDER BY s1.Course, s1.UID'

EXEC(@sql)

DROP TABLE SomeTable

Open in new window

0
 

Author Closing Comment

by:searchsanjaysharma
ID: 38714740
tx
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now