SQL MSSMS How can I select average age from two tablets?

1

Hi guys I am making database in MSSMS and I have question how can I SELECT AVG AGE from two tablets?

Table 1 :
CREATE TABLE Zamestnanci
(id_Zamestnanca int not null,
Meno varchar(50) null,
Priezvisko varchar(50)null,
Adresa varchar(50) null,
Datum_narodenia date null,
PRIMARY KEY (id_Zamestnanca)
);

Table 2 :
CREATE TABLE ObčaniaSR
(id_Občana int not null,
Meno varchar(50) null,
Priezvisko varchar(50) null,
Adresa varchar(50) null,
Datum_narodenia date null,
Zápis_v_trestnom_registry varchar(50) null,
PRIMARY KEY (id_Občana)
);

The value Datum_narodenia means Age .
I've tried this :

*SELECT AVG(Datum_narodenia), Datum_narodenia
FROM Zamestnanci INNER
JOIN ObčaniaSR
ON Datum_narodenia = Datum_narodenia
GROUP BY priemerny vek; *

But it got me errors Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'Datum_narodenia'.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'Datum_narodenia'.
Msg 209, Level 16, State 1, Line 7
Ambiguous column name 'Datum_narodenia'.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'Datum_narodenia'.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'Datum_narodenia'.

Thank you for your answers and advices

2

You need to use the aliases otherwise the engine doens't know where to get the data if the same column name is in both tables. Not sure what you are trying to get since you are trying to get average date and joining on dates. What are you trying to accomplish with this? I'm not going to run this, but average cannot be used on a date field. It will fail

SELECT AVG(z.Datum_narodenia), Datum_narodenia
FROM Zamestnanci as Z
INNER JOIN ObčaniaSR as O
ON Z.Datum_narodenia = O.Datum_narodenia

3

I am trying to SELECT AVERAGE date_of_birth from both tablets

4

If you want the average based on the results from both tables - then you need to combine the tables using UNION or UNION ALL. Then get the results from that...

WITH combined
AS (
SELECT Datum_narodenia FROM Zamestnanci 
UNION ALL
SELECT Datum_narodenia FROM ObčaniaSR
)
SELECT AVG(Datum_narodenia)
  FROM combined;
5

AVG will not work on a date field

;with combined 
as (select cast('1/1/2022' as date) as D1
union all
select cast('11/1/2022' as date) as D1)

select avg(d1) from combined

Msg 8117, Level 16, State 1, Line 8
Operand data type date is invalid for avg operator.

1 Like
6

I solved it with this
;WITH combined AS
(
SELECT Datum_narodenia
FROM Zamestnanci
UNION ALL
SELECT Datum_narodenia
FROM ObčaniaSR
)
SELECT CONVERT(DECIMAL(5,1),
AVG(DATEDIFF(HOUR, Datum_narodenia, GETDATE())/8766.0))
FROM combined;