How to calculate AVG from two tablets

Question

I am creating a database in SSMS and I am trying to calculate AVG from 2 columns from 2 tablets; I wrote :

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

But I got this error:

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

I created 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)
);

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)
);

Datum_narodenia means Date_of_birth from witch I try to calculate AVG.

What is best way to calculate AVG age according to you? Thank you for your answers and advice.

There is nothing wrong with your CTE, did you read the error message? Assuming you're using SQL Server given your MSSMS comment, you cannot use `avg()` on `date` columns. — Stu, Jan 20 '22 at 21:47
@Stu So what do you suggest? I am trying to get an age from date and then get avg from it. Is it somehow possible to do it this way? And yes you are right I am using SQL server — Iceman, Jan 20 '22 at 21:49
The first thing you need then is the *age*, not the *date*, see [this post](https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate) — Stu, Jan 20 '22 at 21:52
@Stu It gave me `30.033994 30 30 ` Do I have to do this with all the dates in both tablets? — Iceman, Jan 20 '22 at 21:56
Aside from the "you can't average a date" issue, the error in the question is because you have a statement _before_ `WITH` that isn't properly terminated with a semi-colon. Please [get in that habit](https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons). — Aaron Bertrand, Jan 20 '22 at 21:58
FYI SSMS is purely a client interface to SQL Server. So you might be using SSMS to create the database, but its being created in SQL Server. — Dale K, Jan 20 '22 at 22:00
@AaronBertrand I added `;` there but i got `Msg 8117, Level 16, State 1, Line 9 Operand data type date is invalid for avg operator.` — Iceman, Jan 20 '22 at 22:03
@Yes I understand you but I am asking if there is way to do it that way I am making it ? — Iceman, Jan 20 '22 at 22:05

Aaron Bertrand · Accepted Answer · 2022-01-20T22:07:22.573

Pretty close calculation of age (lots of nitty-gritty in this answer) uses the difference in hours between the date and now, divided by the number of hours in a year (8766/24 = 365.25), which tries to roughly account for leap years; it is imperfect, but a pretty good trade-off IMHO between simple and right:

SELECT DATEDIFF(HOUR,'19860201',GETDATE())/8766;

So:

;WITH combined AS
(
    SELECT Datum_narodenia 
    FROM Zamestnanci 
    UNION ALL
    SELECT Datum_narodenia 
    FROM ObčaniaSR
)
SELECT AVG(DATEDIFF(HOUR, Datum_narodenia, GETDATE())/8766)
FROM combined;

Now, this does integer math, so you might instead want:

;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;

I got `47.5` which is right AVG from that values. So thank you — Iceman, Jan 20 '22 at 22:11

How to calculate AVG from two tablets

1 Answers1