What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
COURSE of the MONTH
10 days, 14 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Display data from multiple select statements
Posted on 2016-09-14
Need to be able to query an SQL Database for sales figures from 2013,2014,2015, and 1/1/2016 to 8/30/2016
Ideally I would like to get in return the following columns:
slsperid,CustId, Name, TotalPurch2013,TotalPurch2
Here is the query I normally would use to return:
slsperid,CustId, Name, TotalPurch
Query:
select b.custid,
b.SlsperId,
B.name,
sum(a.TotInvc) as TotalPurch
from SOShipHeader a,Customer b
where a.custid=B.custid
and a.InvcDate>='1/1/2015'
and a.InvcDate<='12/31/2015'
and a.Status ='C'
and a.cancelled<>'1'
and b.SlsperID like '%'
and a.CustId>='a'
group by b.custid,b.SlsperId,B.name
order by b.custid
Your assistance in formatting this query to get the return data set would be greatly appreciated.
Ideally I would like to get in return the following columns:
slsperid,CustId, Name, TotalPurch2013,TotalPurch2
Here is the query I normally would use to return:
slsperid,CustId, Name, TotalPurch
Query:
select b.custid,
b.SlsperId,
B.name,
sum(a.TotInvc) as TotalPurch
from SOShipHeader a,Customer b
where a.custid=B.custid
and a.InvcDate>='1/1/2015'
and a.InvcDate<='12/31/2015'
and a.Status ='C'
and a.cancelled<>'1'
and b.SlsperID like '%'
and a.CustId>='a'
group by b.custid,b.SlsperId,B.name
order by b.custid
Your assistance in formatting this query to get the return data set would be greatly appreciated.
[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
3 Comments
Active today
can't you use your client to report in columns?
from the SQL side, you could group by years simply like this:
from the SQL side, you could group by years simply like this:
select b.custid,
b.SlsperId,
B.name,
sum(a.TotInvc) as TotalPurch, year(a.InvcDate) as InvoiceYear
from SOShipHeader a,Customer b
where a.custid=B.custid
and a.Status ='C'
and a.cancelled<>'1'
and b.SlsperID like '%'
and a.CustId>='a'
group by b.custid,b.SlsperId,B.name,b.phone,b.fax,b.ClassId, year(a.InvcDate)
order by b.custid
Active today
select b.custid,
b.SlsperId,
B.name,
SUM(case when a.InvcDate >= '20130101' AND a.InvcDate < '20140101' then a.TotInvc else 0 end) as TotalPurch2013,
SUM(case when a.InvcDate >= '20140101' AND a.InvcDate < '20150101' then a.TotInvc else 0 end) as TotalPurch2014,
SUM(case when a.InvcDate >= '20150101' AND a.InvcDate < '20160101' then a.TotInvc else 0 end) as TotalPurch2015,
SUM(case when a.InvcDate >= '20160101' AND a.InvcDate < '20160901' then a.TotInvc else 0 end) as TotalPurch2016
from SOShipHeader a,Customer b
where a.custid=B.custid
and a.InvcDate>='20130101'
and a.InvcDate<'20160901'
and a.Status ='C'
and a.cancelled<>'1'
and b.SlsperID like '%'
and a.CustId>='a'
group by b.custid,b.SlsperId,B.name
order by b.custid,b.SlsperId
b.SlsperId,
B.name,
SUM(case when a.InvcDate >= '20130101' AND a.InvcDate < '20140101' then a.TotInvc else 0 end) as TotalPurch2013,
SUM(case when a.InvcDate >= '20140101' AND a.InvcDate < '20150101' then a.TotInvc else 0 end) as TotalPurch2014,
SUM(case when a.InvcDate >= '20150101' AND a.InvcDate < '20160101' then a.TotInvc else 0 end) as TotalPurch2015,
SUM(case when a.InvcDate >= '20160101' AND a.InvcDate < '20160901' then a.TotInvc else 0 end) as TotalPurch2016
from SOShipHeader a,Customer b
where a.custid=B.custid
and a.InvcDate>='20130101'
and a.InvcDate<'20160901'
and a.Status ='C'
and a.cancelled<>'1'
and b.SlsperID like '%'
and a.CustId>='a'
group by b.custid,b.SlsperId,B.name
order by b.custid,b.SlsperId
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses
Course of the Month10 days, 14 hours left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.