September 26, 2011 at 2:40 pm
Greetings all,
This one should be fairly simple but I can't seem to get it to work.
I have the following:
SELECT a.deptcode, c.deptname, em.check, em.cash,a.empl_first,a.empl_last, e.[e_mail],a.employee_id, sum(IsNull(em.check,0)) as CheckTotal, sum(IsNull(em.cash,0)) as cashTotal, sum(checkTotal + sum(cashTotal) as grandTotal
FROM ctable c,[e_mail] e,Aempl a,EMamts em WHERE c.divisioncode = em.divisioncode
AND a.empid = e.empid
AND e.empid = em.empid
AND em.empid = '11111'
AND (isnull(em.check, 0)<> 0
OR isnull(em.cash, 0)<> 0) Group by a.deptcode, c.deptname, em.check, em.cash,a.empl_first,a.empl_last, e.[e_mail],a.employee_id
Any ideas?
Thanks a lot in advance
September 26, 2011 at 11:43 pm
What is your requirement and expected result ??
September 27, 2011 at 12:00 am
Besides the expected result, what is the problem with the result set?
Do you have sample data to go with this problem?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2011 at 6:40 am
have 2 columns check and cash, laid out like this:
Check Cash
129.00 00
10.00 00
00 20.00
What I would like to do is sum the sum of the 2.
For instance, the sum of the check is 139.00 and the sum of cash is 20.00
Now, I want to get the sum of the 2 and use it as row colum like:
Total: 159.00
For some reason, my calculation seems to be multiplying the results rather than summing them.
For instance, when I sum the 2, sum(check) + sum(cash), assume that the value of check is 10.00 and the value of cash is 0, instead of getting total value of 10, I get 100.
Both are Money datatypes.
Thanks for asking.
September 27, 2011 at 6:55 am
Greetings!!
Try this:-
select sum(isnull([check],0)) as sum_check, sum(isnull(cash,0)) as sum_cash,
sum(isnull([check],0))+sum(isnull(cash,0)) as Total
from #tbldata
Regards
Palash Gorai
September 27, 2011 at 6:58 am
I would appreciate if you can provide the DDL and DML for creating Sample data, so that we can workout...
regards
Palash Gorai
September 27, 2011 at 7:14 am
Thank you Palash; you are great.
I think I figured out the problem.
THis is sample data for check:
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
20.0000
Cash has similar values.
Notice the extra 00s. I guess I will have to divide by 10 to get actual value like:
select sum(isnull([check],0))/10 as sum_check, sum(isnull(cash,0))/10 as sum_cash,
sum(isnull([check],0))+sum(isnull(cash,0)) /10as Total
from #tbldata
Is there a better option
September 27, 2011 at 7:30 am
First I wouldn't use the old join type, but proper inner joins.
Second, I think it's rather strange that in the query itself as well in the group by, the summed columns are listed.
Third, you seem you're using aliases as column names in the same query. As far as I know aliases in the query are only possible with using subqueries and in the order by.
Fourth, the piece of code sum(checkTotal + sum(cashTotal) as grandTotal has an incorrect syntax.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
September 27, 2011 at 7:34 am
Hi Ronald,
I agree with only your fourth argument.
Thanks a lot for taking the time to give your feedback.
Much appreciated.
September 27, 2011 at 10:10 am
Ronald H (9/27/2011)
First I wouldn't use the old join type, but proper inner joins.Second, I think it's rather strange that in the query itself as well in the group by, the summed columns are listed.
Third, you seem you're using aliases as column names in the same query. As far as I know aliases in the query are only possible with using subqueries and in the order by.
Fourth, the piece of code sum(checkTotal + sum(cashTotal) as grandTotal has an incorrect syntax.
1) The old style joins are not any faster or slower but using an actual inner join is preferable for a few reasons. It is easier to read for most people. More importantly with the old style joins it is really easy to accidentally get a cross join.
3) That is correct. A column alias is just that. It can not be used as a reference to a different column in the select statement as you are doing. In your case you would need to perform the full calculation for checkTotal and cashTotal.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2011 at 7:11 pm
1. To sum the sums, it's a lot simpler to sum the sum of each row. In other words, instead of trying to add CheckTotal and CashTotal, you could just add Cash and Check in each row and feed that to the Sum() function:sum(IsNull(em.check,0) + IsNull(em.cash,0)) as EmployeeTotal Then, use the "WITH ROLLUP" option of the GROUP BY to get a grand total at the end. This, I think, may be why Ronald said it's strange that you've got em.Check and em.Cash AND you have sum () of each, all in the select list. To get the sum of multiple rows for an employee, you would normally have employee stuff in the Select list along with the aggregate Sum() functions. If you wanted to list all the individual em. rows, then you wouldn't have the Sum() functions at all, but would rather let the ROLLUP do that job for you.
2. The explicit JOIN syntax is, as have others said, much preferable to the code you posted. It distinguished the join criteria from the row selection criteria and by consensus makes the intent much clearer than the laundry list of tables syntax.
3. The "looks like it's multiplying" problem is coming from the attempt to add the two category totals for each row. See number 1. above.
4. The "extra" zeroes, showing thousandths and hundred-thousandths, are part of the definition of the money datatype. Dividing by 100 would not eliminate those decimal places, but would make the result understate the values. 123.4500 would become 1.2345, and I doubt that's what you want. Take care of the trailing zeros in your presentation code or, if it bothers you in SSMS, convert the answer to another datatype with the desired precision.
5. Readability counts. This query may be something you intend only for yourself, but using a clear, easy to read format is still a good habit to nurture. You may find yourself looking back at this query to use as a guide for another case and you want to be kind to your future self. If this query is part of something done for work, someone else may have to maintain/change it to meet future requirements. Be kind to that person. I've only gone part way in spliting column names onto separate lines, but even just that makes it easier to read than scrolling back and forth.
Here's some code that may help you get started on the right track. Without a full test bed with all the tables, I've commented the code that would have referenced them.
declare @em table (EmpID int, [check] money null, cash money null)
insert @em
Select 21, 1244.4300, 19.55 union All
Select 31, null, 00.55 union All
Select 21, 1234.33, 11.55 union All
Select 51, 1234.3312, null union All
Select 51, 1334.45, 55.55
SELECT
-- a.deptcode
--, c.deptname
em.EmpID
--, em.[check]
--, em.cash
--,a.empl_first
--,a.empl_last
--, e.[e_mail]
--,a.employee_id
, sum(IsNull(em.[check],0)) as CheckTotal
, sum(IsNull(em.cash,0)) as cashTotal
--, sum(checkTotal + sum(cashTotal) as grandTotal
, sum(IsNull(em.[check],0) + IsNull(em.cash,0)) as EmpTotal
FROM @em em
--ctable c
--join
-- EMamts em on c.divisioncode = em.divisioncode
--join [e_mail] e on e.empid = em.empid
--join Aempl a on a.empid = e.empid
WHERE
(isnull(em.[check], 0)<> 0 -- Only report for rows with a non-zero Check or Cash amount
or isnull(em.cash, 0)<> 0) --... so if a row has zero for both, it's not reported.
Group by EmpID
With Rollup
--- And a version with each data row reported. You can figure out how to put the joins back in.
SELECT
em.EmpID
, em.[check]
, em.cash
, sum(IsNull(em.[check],0) + IsNull(em.cash,0)) as EmpTotal
FROM @em em
WHERE
(isnull(em.[check], 0)<> 0 -- Only report for rows with a non-zero Check or Cash amount
or isnull(em.cash, 0)<> 0) --... so if a row has zero for both, it's not reported.
Group by EmpID
, em.[check]
, em.cash
With Rollup
September 28, 2011 at 9:50 am
John, thank you very very much for taking the time to put this elaborate explanations plus the code.
It is much appreciated.
Only problem I have so far is that if I filter the records with an employeeid; where employeeid ='11111', for some reason, I no longer see the totals or grandtotals.
What could I possibly be doing wrong?
I take that back boss. However, it is showing lots of duplications.
September 28, 2011 at 10:00 am
I (and others, I'm sure) would rather not guess. Post the failing code so folks can give a crack at finding the problem.
September 28, 2011 at 10:27 am
Actually, John, it isn't a duplicate.
You did such a fantastic job. It is the Rollup that adds the extra null as it "rolls" up the totals.
It would have been great *if* I could take your second query and sum the sum so the nulls won't appear.
So I could get grandTotal but I guess I can leave with what you have so far.
Many thanks again. I learned a thing or two from your detailed explanations.
September 28, 2011 at 11:19 am
I'm glad to hear you have something that works for you. The "extra" NULLS in the result set could be made more understandable by adding GROUPING() functions to the Select. Look that one up in BOL for the basics, then look around on the web for examples that use its binary value (1=this is from a rollup, 0=this is from the basic group by) in a case statement to control labels in the results. You should be able to figure out how to add a results column that has "SubTotal" or "Grand Total" in those lines, and either "Detail" or spaces in the others.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply