Query...combine results of one column
-
I have a query that some very kind souls helped me get working. They query works great, however the results are not formatting the way I want them to.
The query is below, and it is displaying individual results for every user like this(sorry the columns don't line up):
Uname UnameFirst UnameLast WCid totBilledUnits
---------------------------------------------------------------------
asmith aaron smith wc231 10
asmith aaron smith wc191 25
asmith aaron smith wc901 15
tbrown tom brown wc991 10
tbrown tom brown wc300 20
tbrown tom brown wc642 10
tbrown tom brown wc222 12
jdavis joe davis wc131 10
jdavis joe davis wc140 20
I would like the colums to just show the totals like:
Uname UnameFirst UnameLast totBilledUnits
-------------------------------------------------------------
asmith aaron smith 50
tbrown tom brown 52
jdavis joe davis 30
Here is the query:
SELECT U.Uname, U.UnameFirst, U.UnameLast, WC.WCid,
COUNT( WC.WCid) AS totWorkedDays,
SUM( WC.WCtaskAmount) AS totWorkedUnits,
SUM( WC.WCbillableAmount) AS totBilledUnits,
ROUND( SUM( WC.WCtaskAmount * Urate), 2) AS totWages,
ROUND( SUM( WC.WCmileage), 2) AS totMileage,
WE.totExpenses
FROM workcompleted WC
INNER JOIN users U ON( WC.WCemployeeID = U.Uname)
INNER JOIN internaloffice O ON( U.UinternalOfficeID = O.IOid)
INNER JOIN worktask WT ON( WC.WClinkItemID = WT.WTid)
INNER JOIN workrequest WR ON( WT.WTlinkItemID = WR.WRid)
INNER JOIN subjectrecord SR ON( WR.WRsubjectRecordID = SR.SRid)
LEFT JOIN( SELECT WXlinkItemID, ROUND( SUM( WXamount), 2) AS totExpenses
FROM workexpense GROUP BY WXlinkItemID) WE ON( WC.WCid = WE.WXlinkItemID)
WHERE WC.WCcompletedDate BETWEEN '1/04/2007 00:00:00' AND '2/2/2007 23:59:59'
AND WC.WCcompletedDate IS NOT NULL
AND WC.WCtaskTypeID <> 'BIWTNON'
AND U.UworkgroupTypeID <> 'TRUSXXX'
GROUP BY U.Uname, U.UnameLast, U.UnameFirst, WE.WXlinkItemID, WC.WCid, WE.totExpenses
ORDER BY U.UnameLast, U.UnameFirst
Is there a way to do this with this query?
Thank you
-
I'm sorry to mention it can be handy to learn some basics regarding Structured Query Language. Invest some time reading about the "select" statement in books online.
(this is not meant to be rude, but constructive !) This combining is called a group by operation. Modify your select-list and grouping part.
SELECT U.Uname, U.UnameFirst, U.UnameLast
-- , WC.WCid,
-- COUNT( WC.WCid) AS totWorkedDays,
-- SUM( WC.WCtaskAmount) AS totWorkedUnits,
SUM( WC.WCbillableAmount) AS totBilledUnits,
-- ROUND( SUM( WC.WCtaskAmount * Urate), 2) AS totWages,
-- ROUND( SUM( WC.WCmileage), 2) AS totMileage,
-- WE.totExpenses
FROM workcompleted WC
INNER JOIN users U ON( WC.WCemployeeID = U.Uname)
INNER JOIN internaloffice O ON( U.UinternalOfficeID = O.IOid)
INNER JOIN worktask WT ON( WC.WClinkItemID = WT.WTid)
INNER JOIN workrequest WR ON( WT.WTlinkItemID = WR.WRid)
INNER JOIN subjectrecord SR ON( WR.WRsubjectRecordID = SR.SRid)
LEFT JOIN( SELECT WXlinkItemID, ROUND( SUM( WXamount), 2) AS totExpenses
FROM workexpense GROUP BY WXlinkItemID) WE ON( WC.WCid = WE.WXlinkItemID)
WHERE WC.WCcompletedDate BETWEEN '1/04/2007 00:00:00' AND '2/2/2007 23:59:59'
AND WC.WCcompletedDate IS NOT NULL
AND WC.WCtaskTypeID <> 'BIWTNON'
AND U.UworkgroupTypeID <> 'TRUSXXX'
GROUP BY U.Uname, U.UnameLast, U.UnameFirst
--, WE.WXlinkItemID, WC.WCid, WE.totExpenses
ORDER BY U.UnameLast, U.UnameFirst
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
No need to be sorry. I have BOL and I've been using it for over a year now. It's a wonderful resource. But I've tried all sorts of various combinations of my query and they all return similiar results. I only ask questions on this wonderful forum when I am at my wits end. Sometimes you just need another set of eyes to take a quick look at something and maybe they'll see something you overlooked.
-
I totaly agree !
And sometimes "time" also plays a roll in a situation.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
yeah like staying up til 5am looking at the screen until your eyes feel like they are going to fall out.
-
I have this query(below) that returns the type of format I want, but these results are wrong. I am not sure how to combine the two queries to get the results from the first, and the formatting from the 2nd.
select Uname, UnameFirst, UnameLast,
count(WCid) as totWorkedDays,
sum(WCtaskAmount) as totWorkedUnits,
sum(WCbillableAmount) as totBilledUnits,
round(sum(WCtaskAmount * Urate),2) as totWages,
round(sum(Umileage * WCmileage), 2) as totMileage,
round(sum(WCmileage), 2) as totMileage,
round(sum(WXamount), 2) as totExpenses
from workrequest, subjectrecord, worktask, users, internaloffice,
workcompleted left outer join workexpense on WXlinkItemID = WCid
where WCcompletedDate BETWEEN '1/04/2007 00:00:00' AND '2/2/2007 23:59:59'
and WCcompletedDate is not null
and WCemployeeID = Uname
and WCtaskTypeID 'BIWTNON'
and WClinkItemID = WTid
and WTlinkItemID = WRid
and UinternalOfficeID = IOid
and UworkgroupTypeID 'TRUSXXX'
and WRsubjectRecordID = SRid
group by Uname, UnameLast, UnameFirst
ORDER BY UnameLast, UnameFirst
-
can you add tablealiasses ? That eases reading and interpreting the query ?
e.g select t1.Uname
from mytable t1
, myview x2
where t1.idno = x2.colxyz
and t1.Ubirthdate = '2007-01-01'
or
select t1.Uname
from mytable t1
left join myview x2
on t1.idno = x2.colxyz
where t1.Ubirthdate = '2007-01-01'
---------
what you can use to get you going ...
select
MyTbExp.Uname, MyTbExp.UnameFirst, MyTbExp.UnameLast,
sum
(MyTbExp.totWorkedDays) as totWorkedDays,
sum
(MyTbExp.totWorkedUnits) as totWorkedUnits,
sum
(MyTbExp.totBilledUnits) as totBilledUnits,
sum
(MyTbExp.totWages) as totWages,
sum
(MyTbExp.totMileage_01) as totMileage,
sum
(MyTbExp.totMileage) as totMileage,
MyTbExp
.totExpenses
from
(
SELECT
U.Uname, U.UnameFirst, U.UnameLast, WC.WCid,
COUNT
( WC.WCid) AS totWorkedDays,
SUM
( WC.WCtaskAmount) AS totWorkedUnits,
SUM
( WC.WCbillableAmount) AS totBilledUnits,
ROUND
( SUM( WC.WCtaskAmount * Urate), 2) AS totWages,
round
(sum(Umileage * WCmileage), 2) as totMileage_01,
ROUND
( SUM( WC.WCmileage), 2) AS totMileage,
WE
.totExpenses
FROM
workcompleted WC
INNER
JOIN users U ON( WC.WCemployeeID = U.Uname)
INNER
JOIN internaloffice O ON( U.UinternalOfficeID = O.IOid)
INNER
JOIN worktask WT ON( WC.WClinkItemID = WT.WTid)
INNER
JOIN workrequest WR ON( WT.WTlinkItemID = WR.WRid)
INNER
JOIN subjectrecord SR ON( WR.WRsubjectRecordID = SR.SRid)
LEFT
JOIN( SELECT WXlinkItemID, ROUND( SUM( WXamount), 2) AS totExpenses
FROM workexpense
GROUP BY WXlinkItemID) WE
ON( WC.WCid = WE.WXlinkItemID)
WHERE
WC.WCcompletedDate BETWEEN '1/04/2007 00:00:00' AND '2/2/2007 23:59:59'
AND
WC.WCcompletedDate IS NOT NULL
AND
WC.WCtaskTypeID <> 'BIWTNON'
AND
U.UworkgroupTypeID <> 'TRUSXXX'
GROUP
BY U.Uname, U.UnameLast, U.UnameFirst
, WE.WXlinkItemID, WC.WCid, WE.totExpenses
)
MyTbExp
GROUP
BY MyTbExp.Uname, MyTbExp.UnameFirst, MyTbExp.UnameLast,MyTbExp.totExpenses
ORDER
BY U.UnameLast, U.UnameFirst
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Sometimes (not necessarily this time) it is easier or more efficient to take the result set of your current query, and then perform additional processing on it. 2 ways to do it are:
- Insert the results into a temporary table or a table variable and then perform additional processing of the data.
- Use the original query as a dynamic table and perform additional processing of the data.
I generally lean toward the first option unless the original query is small and fast. For large queries or recordsets, breaking it up into seperate smaller actions can often speed up processing. The first option is also handy if you need to perform several different queries on the same subset of data because you only have to perform the core query once.
Note that in both examples, I moved the Order By to the outer query.
Example # 1:
Declare @Results Table (Uname varchar(100) null,
UnameFirst varchar(100) null,
UnameLast varchar(100) null,
WCID varchar(10) not null,
totWorkedDays int not null,
totWorkedUnits int not null,
totBilledUnits int not null,
totWages int not null,
totMileage int not null,
totExpenses int not null)
Insert Into @Results
SELECT U.Uname, U.UnameFirst, U.UnameLast, WC.WCid,
COUNT( WC.WCid) AS totWorkedDays,
SUM( WC.WCtaskAmount) AS totWorkedUnits,
SUM( WC.WCbillableAmount) AS totBilledUnits,
ROUND( SUM( WC.WCtaskAmount * Urate), 2) AS totWages,
ROUND( SUM( WC.WCmileage), 2) AS totMileage,
WE.totExpenses
FROM workcompleted WC
INNER JOIN users U ON( WC.WCemployeeID = U.Uname)
INNER JOIN internaloffice O ON( U.UinternalOfficeID = O.IOid)
INNER JOIN worktask WT ON( WC.WClinkItemID = WT.WTid)
INNER JOIN workrequest WR ON( WT.WTlinkItemID = WR.WRid)
INNER JOIN subjectrecord SR ON( WR.WRsubjectRecordID = SR.SRid)
LEFT JOIN( SELECT WXlinkItemID, ROUND( SUM( WXamount), 2) AS totExpenses
FROM workexpense GROUP BY WXlinkItemID) WE ON( WC.WCid = WE.WXlinkItemID)
WHERE WC.WCcompletedDate BETWEEN '1/04/2007 00:00:00' AND '2/2/2007 23:59:59'
AND WC.WCcompletedDate IS NOT NULL
AND WC.WCtaskTypeID <> 'BIWTNON'
AND U.UworkgroupTypeID <> 'TRUSXXX'
GROUP BY U.Uname, U.UnameLast, U.UnameFirst, WE.WXlinkItemID, WC.WCid, WE.totExpenses
Select Uname, UnameFirst, UnameLast, Sum(totBilledUnits) As totBilledUnits
From @Results
Group By Uname, UnameFirst, UnameLast
ORDER BY UnameLast, UnameFirst
Example # 2:
Select Uname, UnameFirst, UnameLast, Sum(totBilledUnits) As totBilledUnits
From (SELECT U.Uname, U.UnameFirst, U.UnameLast, WC.WCid,
COUNT( WC.WCid) AS totWorkedDays,
SUM( WC.WCtaskAmount) AS totWorkedUnits,
SUM( WC.WCbillableAmount) AS totBilledUnits,
ROUND( SUM( WC.WCtaskAmount * Urate), 2) AS totWages,
ROUND( SUM( WC.WCmileage), 2) AS totMileage,
WE.totExpenses
FROM workcompleted WC
INNER JOIN users U ON( WC.WCemployeeID = U.Uname)
INNER JOIN internaloffice O ON( U.UinternalOfficeID = O.IOid)
INNER JOIN worktask WT ON( WC.WClinkItemID = WT.WTid)
INNER JOIN workrequest WR ON( WT.WTlinkItemID = WR.WRid)
INNER JOIN subjectrecord SR ON( WR.WRsubjectRecordID = SR.SRid)
LEFT JOIN( SELECT WXlinkItemID, ROUND( SUM( WXamount), 2) AS totExpenses
FROM workexpense GROUP BY WXlinkItemID) WE ON( WC.WCid = WE.WXlinkItemID)
WHERE WC.WCcompletedDate BETWEEN '1/04/2007 00:00:00' AND '2/2/2007 23:59:59'
AND WC.WCcompletedDate IS NOT NULL
AND WC.WCtaskTypeID <> 'BIWTNON'
AND U.UworkgroupTypeID <> 'TRUSXXX'
GROUP BY U.Uname, U.UnameLast, U.UnameFirst, WE.WXlinkItemID, WC.WCid, WE.totExpenses) As DynTable
Group By Uname, UnameFirst, UnameLast
ORDER BY UnameLast, UnameFirst
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply