SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 only ONE sum of values from different queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

berkov
Starting Member

Czech Republic
2 Posts

Posted - 06/15/2013 :  18:28:53  Show Profile  Reply with Quote

Hey ho!

Let's say I have following table:
Date | Empoyee1 | DailyWageFor1| Empoyee2 | DailyWageFor2| ... | ... | ... etc

I would like to sum up daily wages for a specific employee (within a specific period but just to have it a bit clearer let’s leave this for now) in a following way:
John - 2000 EUR

There is only one problem, that John can sometimes appear in Empoyee1 and sometimes in Empoyee2 where obviously then his daily wage would be stored accordingly in DailyWageFor1 or DailyWageFor2 (depending on which order it was keyed in )

I have the following query:
SELECT Empoyee1 AS EmplyeeName, SUM(DailyWageFor1) AS DailyPay
FROM table1
WHERE Empoyee1 = ‘John’
GROUP BY Empoyee1
UNION
SELECT Empoyee2, SUM(DailyWageFor2)
FROM table1
WHERE Empoyee2= ' John '
GROUP BY Empoyee2

Unfortunately in this query instead of getting one row for John who earned 2000, I get e.g. this:
John - 1500 EUR
John – 500 EUR

Can i sum it up somehow to just one row?

I will only add that I’m a bit limited with sql, the reason for this is that I send those requests to Access via ADO (COM) from C++ (Winapi) and there are certain things that this communication cannot serve, e.g connecting two queries by operator + (I believe this is possible on “standard”, full sql engines) like this (SELECT xxx FROM yyy ....) + (SELECT aaa from bbb ..).. In ADO It always needs to be one long statement (like my query above), multiple separate ones are not allowed.

p.s.
What i wanna get at the end is a list of all employees at once (each row for each employee), like this:
John 2000 eur
Angel 1500 eur
Jack 3001 eur
But i hope that if someone helps me with showing only one line for one employee than would easly handle the rest by myself.


Thanks in advance.
b.

bitsmed
Constraint Violating Yak Guru

405 Posts

Posted - 06/15/2013 :  19:09:56  Show Profile  Reply with Quote
First of all, fieldname seems to be spelled wrong. Are you sure the fieldname is Empoyee1?
Secondly when names are in Emp(l)oyee2, is first and last character always a Space?
In the folowing sql, I assume fieldname is spelled Employee and no spaces surrounding the names:

select EmployeeName
      ,sum(DailyPay) as DailyPay
  from (select Employee1 as EmployeeName
              ,sum(DailyWageFor1) as DailyPay
          from table1
         where Employee1='John'
         group by Employee1
        union all
        select Employee2
              ,sum(DailyWageFor2)
          from table1
         where Employee2='John' 
         group by Employee2
       )
 group by EmployeeName
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/15/2013 :  19:51:16  Show Profile  Reply with Quote
If I understand OP's request correctly, may be this what he/she wants:


DECLARE @table1 TABLE (Date1 DATE, Employee1 VARCHAR(20), DailyWageFor1 MONEY, Employee2 VARCHAR(20), DailyWageFor2 MONEY);

INSERT INTO @table1 VALUES
('20130401', 'John', 2000.00, 'John', 2500),
('20130401', 'James', 2500.00, 'James', 3500),
('20130401', 'Joe', 1000.00, 'Joe', 1500),
('20130402', 'John', 2200.00, 'John', 2600);

SELECT Date1, Employee1, SUM(DailyWageFor1) FROM 
(SELECT Date1, Employee1, DailyWageFor1 FROM @table1
UNION ALL
SELECT Date1, Employee2, DailyWageFor2 FROM @table1
)A GROUP BY DATE1, Employee1;

-- OR

SELECT Employee1, SUM(DailyWageFor1) FROM 
(SELECT Date1, Employee1, DailyWageFor1 FROM @table1
UNION ALL
SELECT Date1, Employee2, DailyWageFor2 FROM @table1
)A GROUP BY  Employee1;

Go to Top of Page

berkov
Starting Member

Czech Republic
2 Posts

Posted - 06/16/2013 :  05:25:31  Show Profile  Reply with Quote
@bitsmed,
you're right, the fieldname was spelled incorrectly but it was a typo only in here, i had it correctly in my statement.
There are also no spaces at the beginning/end of any of the fields.

And YOUR STATEMENT WORKED!

i'm getting only one row!

i did not know you can connect two statements like this.

thanks a lot!

p.s. there is only one note here. Apparently i cannot use the same sum name twice (in top and subordinate statement) because i get an error "Circular reference caused by alias 'DailyPay' in query definition's SELECT list". I had to rename one of the 'DailyPay's to something else so i made the first (top) SELECT to be e.g.:

select EmployeeName ,sum(DailyPay) as XDailyPayX from (.....)

Edited by - berkov on 06/16/2013 05:34:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000