Out put from two queries into one

Question

0.00/5 (No votes)

See more:

dear All

I have two queries

select a.brcd,round(sum(outamt)/1000,0) amt from cis 
group by a.brcd order by amt desc
)


and 

<pre>select brcd, round(sum(outamt)/1000,0) amt from cis  where   assetcd !='IR00001' group by a.brcd order by amt desc

how to merge two queries so that

From table with region wise branch sales,
I wish to select top ten branches with ,
sum of sales with maximum sales excluding an item< given code> and total sale of those branches

What I have tried:

select * from(
    select a.brcd,
    round(sum(b.outamt)/1000,0) sale,
    round(sum(a.outamt)/1000,0) totalsale from cis a
    inner join
    (select brcd, outamt from cis  where   assetcd !='IR00001') b 
    on b.brcd=a.brcd
    group by a.brcd order by sale desc
)  where    rownum <11;

Posted 31-Oct-18 21:35pm

Shilpa_g2

Updated 1-Nov-18 2:34am

Add a Solution

Comments

OriginalGriff 1-Nov-18 3:54am

That doesn't make a lot of sense as it stands - perhaps if you gave us a short example of the input and output you need, it would help?

Use the "Improve question" widget to edit your question and provide better information.

Sarita B Patil 1-Nov-18 4:36am

Try This

CREATE TABLE [dbo].[StudentMaster](
[StudentId] [numeric](18, 0) NOT NULL,
[StudentName] [varchar](50) NULL
) ON [PRIMARY]

GO
INSERT INTO StudentMaster(StudentID, StudentName) VALUES (1,'Garry')
INSERT INTO StudentMaster(StudentID, StudentName) VALUES (2,'Alex')
INSERT INTO StudentMaster(StudentID, StudentName) VALUES (3,'Sam')
INSERT INTO StudentMaster(StudentID, StudentName) VALUES (4,'Paul')
INSERT INTO StudentMaster(StudentID, StudentName) VALUES (5,'Ben')
SELECT * FROM StudentMaster
GO

CREATE TABLE [dbo].[Courses](
[CourseID] [int] NOT NULL,
[CourseName] [nvarchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[CourseID] 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

INSERT INTO Courses(CourseId,CourseName) VALUES (1,'C#')
INSERT INTO Courses(CourseId,CourseName) VALUES (2,'ASP.Net')
INSERT INTO Courses(CourseId,CourseName) VALUES (3,'MVC')
INSERT INTO Courses(CourseId,CourseName) VALUES (4,'WCF')
INSERT INTO Courses(CourseId,CourseName) VALUES (5,'Share Point')
INSERT INTO Courses(CourseId,CourseName) VALUES (6,'WPF')
INSERT INTO Courses(CourseId,CourseName) VALUES (7,'SQL Server')
INSERT INTO Courses(CourseId,CourseName) VALUES (8,'JQuery')
SELECT * FROM Courses

GO

CREATE TABLE [dbo].[StudentCourses](
[StudentID] [int] NULL,
[CourseID] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,1)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,3)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,5)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,2)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,4)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,5)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,3)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,6)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,7)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,8)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,1)
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,2)
SELECT * FROM StudentCourses

select t11.StudentID,StudentMaster.StudentName,t11.CourseNames from
(SELECT StudentID,
CourseNames=STUFF
(
(
SELECT DISTINCT ', '+ CAST(g.CourseName AS VARCHAR(MAX))
FROM Courses g,StudentCourses e
WHERE g.CourseID=e.CourseID and e.StudentID=t1.StudentID
FOR XMl PATH('')
),1,1,''
)
FROM StudentCourses t1
GROUP BY StudentID ) as t11
INNER JOIN StudentMaster ON StudentMaster.StudentID = t11.StudentID

Output will as follows : -

StudentID StudentName CourseNames
1 Garry C#, MVC, Share Point
2 Alex ASP.Net, Share Point, WCF
3 Sam MVC, WPF
4 Paul JQuery, SQL Server
5 Ben ASP.Net, C#

Herman<T>.Instance 1-Nov-18 5:18am

Is this homework?

Santosh kumar Pithani 1-Nov-18 4:59am

Hi, where you stuck ;what you expecting ?

3 solutions

Solution 3

SQL

--I hope this solution may help you
select 
   A.brcd,
   round(sum(A.outamt)/1000,0) amt1,
   (
     select round(sum(B.outamt)/1000,0)   FROM cis AS B
        where  
           B.assetcd<>'IR00001' 
       AND B.rownum <11
       AND A.brcd=B.brcd
  ) AS Amt2  
from cis AS A  group by A.brcd order by amt1 DESC,Amt2 DESC

Posted 1-Nov-18 2:35am

Santosh kumar Pithani

Updated 1-Nov-18 2:47am

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shilpa_g2 · Accepted Answer · 2018-10-31T23:39:00

Solution 1

resolved by using following query

select * from
 (select * from
(select brcd, round(sum(outamt)/1000,0) amt from cis  where   assetcd !='IR00001'
group by brcd order by amt desc)  where    rownum <11)  a

inner join

(select brcd,round(sum(outamt)/1000,0) amt2  from cis  group by brcd) b

on a.brcd=b.brcd
order by amt desc
;

any better way to do so.

Posted 31-Oct-18 23:39pm

Shilpa_g2

Updated 31-Oct-18 23:51pm

v2

Comments

CHill60 1-Nov-18 8:18am

This seems to be combining a solution with a question. Is your problem resolved?

Santosh kumar Pithani 1-Nov-18 8:22am

SQLServer may not accept order by inside derived table without(top,xml,offset)
1."select brcd, round(sum(outamt)/1000,0) amt from cis where assetcd !='IR00001'
group by brcd order by amt desc"

2."rownum" column doesn't given inside derived table then how its possible to give as filter condition outside derived table?

CHill60 1-Nov-18 8:28am

This does not work at all. Invalid ORDER BY, Incorrect syntax, missing aliases.
Use the "Improve Question" link to include some sample data in your question and some expected results

Shilpa_g2 2-Nov-18 1:14am

I am working on oracle not on sql server.

Shilpa_g2 · Accepted Answer · 2018-11-01T00:00:00

Solution 2

my problem

100 branches selling 3 items. we have to find 10 branches with maximum sale where sale amount exclude of one item(2 items)

and also

total sale of those branches(all 3 items )

Posted 1-Nov-18 0:00am

Shilpa_g2

Comments

CHill60 1-Nov-18 8:18am

This is not a solution!