select LOGIN from users where status ='cashier'
select kassir,sum(CAST(replace(summa,',','.') as float)) as 'summa_prikhodi' from manager_prikhodi group by kassir
select kassir, SUM(CAST(replace(k_vozvratu_kassir,',','.') as float )) as 'summa_vozvrat' from vozvrat group by kassir
select cashier, SUM(CAST(replace(k_oplate,',','.') as float )) as 'summa_prodazhi' from bilet group by cashier
You are gonna have to check this and make adjustments as i wrote it off top of my head and it probably contains typos etc. Google LEFT JOIN for more information as thats what you need to get the output how you want it.
SELECT LOGIN, sum(CAST(replace(mp.summa,',','.') as float)) AS 'summa_prikhodi', SUM(CAST(replace(v.k_vozvratu_kassir,',','.') as float )) as 'summa_vozvrat', SUM(CAST(replace(b.k_oplate,',','.') as float )) as 'summa_prodazhi'
FROM users AS u
LEFT JOIN manager_prikhodi AS mp ON mp.kassir = u.LOGIN
LEFT JOIN vozvrat AS v ON v.kassir = u.LOGIN
LEFT JOIN bilet AS b on b.cashier = u.LOGIN
WHERE status = 'cashier'
GROUP BY u.kassir
SELECT LOGIN,
sum(CAST(replace(summa,',','.') as float)) as 'summa_prikhodi',
SUM(CAST(replace(k_vozvratu_kassir,',','.') as float )) as 'summa_vozvrat',
SUM(CAST(replace(k_oplate,',','.') as float )) as 'summa_prodazhi'
FROM users u
inner join manager_prikhodi mp ON mp.kassir = u.LOGIN
inner join vozvrat v ON v.kassir = u.LOGIN
inner join bilet b ON b.cashier = u.LOGIN
WHERE u.status = 'cashier'
Msg 8120, Level 16, State 1, Line 1
Column 'users.login' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'users.login' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'k_vozvratu_kassir'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'k_oplate'.
SELECT u.LOGIN,
sum(CAST(replace(mp.summa,',','.') as float)) as 'summa_prikhodi',
SUM(CAST(replace(v.k_vozvratu_kassir,',','.') as float )) as 'summa_vozvrat',
SUM(CAST(replace(b.k_oplate,',','.') as float )) as 'summa_prodazhi'
FROM users u
left join manager_prikhodi mp ON mp.kassir = u.LOGIN
left join vozvrat v ON v.kassir = u.LOGIN
left join bilet b ON b.cashier = u.LOGIN
WHERE u.status = 'cashier'
jfr
Member
229 Points
347 Posts
a query for four tables
Nov 26, 2012 08:20 AM|LINK
Hi! i'm getting:
But I would like to get it.
Thank you!
teh munk
Participant
1466 Points
297 Posts
Re: a query for four tables
Nov 26, 2012 08:32 AM|LINK
You are gonna have to check this and make adjustments as i wrote it off top of my head and it probably contains typos etc. Google LEFT JOIN for more information as thats what you need to get the output how you want it.
jfr
Member
229 Points
347 Posts
Re: a query for four tables
Nov 26, 2012 08:34 AM|LINK
teh munk,
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'status'.
chaaraan
Contributor
2170 Points
484 Posts
Re: a query for four tables
Nov 26, 2012 09:02 AM|LINK
Hi
Please check this query
SELECT LOGIN,
sum(CAST(replace(summa,',','.') as float)) as 'summa_prikhodi',
SUM(CAST(replace(k_vozvratu_kassir,',','.') as float )) as 'summa_vozvrat',
SUM(CAST(replace(k_oplate,',','.') as float )) as 'summa_prodazhi'
FROM users u
inner join manager_prikhodi mp ON mp.kassir = u.LOGIN
inner join vozvrat v ON v.kassir = u.LOGIN
inner join bilet b ON b.cashier = u.LOGIN
WHERE u.status = 'cashier'
Regards,
Charan
teh munk
Participant
1466 Points
297 Posts
Re: a query for four tables
Nov 26, 2012 09:06 AM|LINK
Sorry.
jfr
Member
229 Points
347 Posts
Re: a query for four tables
Nov 26, 2012 09:11 AM|LINK
teh munk,
Msg 8120, Level 16, State 1, Line 1
Column 'users.login' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
jfr
Member
229 Points
347 Posts
Re: a query for four tables
Nov 26, 2012 09:12 AM|LINK
chaaraan,
Msg 8120, Level 16, State 1, Line 1
Column 'users.login' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'k_vozvratu_kassir'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'k_oplate'.
jfr
Member
229 Points
347 Posts
Re: a query for four tables
Nov 26, 2012 09:14 AM|LINK
there is no relationship between tables, is it possible to use left join?
chaaraan
Contributor
2170 Points
484 Posts
Re: a query for four tables
Nov 26, 2012 09:45 AM|LINK
Hi
Could you please try with the below query
SELECT u.LOGIN,
sum(CAST(replace(mp.summa,',','.') as float)) as 'summa_prikhodi',
SUM(CAST(replace(v.k_vozvratu_kassir,',','.') as float )) as 'summa_vozvrat',
SUM(CAST(replace(b.k_oplate,',','.') as float )) as 'summa_prodazhi'
FROM users u
left join manager_prikhodi mp ON mp.kassir = u.LOGIN
left join vozvrat v ON v.kassir = u.LOGIN
left join bilet b ON b.cashier = u.LOGIN
WHERE u.status = 'cashier'
Regards,
Charan
teh munk
Participant
1466 Points
297 Posts
Re: a query for four tables
Nov 26, 2012 09:51 AM|LINK
As long as you can provide keys to link the tables you can do it.
Add u.LOGIN to your Group By Clause.