only ONE sum of values from different queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

only ONE sum of values from different queries

Author

Topic

berkov
Starting Member

2 Posts

Posted - 2013-06-15 : 18:28:53

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
Aged Yak Warrior

545 Posts

Posted - 2013-06-15 : 19:09:56

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-15 : 19:51:16

If I understand OP's request correctly, may be this what he/she wants:
[CODE]

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;

[/CODE]

berkov
Starting Member

2 Posts

Posted - 2013-06-16 : 05:25:31

@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 (.....)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources