Merge two select quiries

1

How do I merge these two quiries - I need to sum the No of Antal_dage returned in the first query?

select Fornavn, Efternavn, Start_dato, Slut_dato, Antal_dage
from Medarbejder join Fravær as Fravær on fravær.medarbejder = medarbejder.id
where Fornavn = 'Susanne' and Start_dato >'2020-01-24'

and

select sum(Antal_dage)
from Fravær;

2

What is the primary key of fravr

3

Thanks for your quick reply. The PK is ID and the table also has a foreign key which is Medarbejder_ID.

4

do you want to sum it all or for individual people, company or, not sure what language is so hard to understand?

select Fornavn, Efternavn, Start_dato, Slut_dato, Antal_dage, bonbon
  from Medarbejder m
  join Fravær as f
    on s.medarbejder = m.id
  join (select sum(Antal_dage) bonbon
          from Fravær group by Fornavn 
	   ) s on s.ID = f.ID
 where Fornavn = 'Susanne' 
   and Start_dato >'2020-01-24'
5

Some example data - and expected results would help here. When you say 'merge' - what exactly do you mean?

Do you want an additional column with an overall total? If so - lookup SUM() OVER()...

If you want a 'totals' row then that is not something you want to do in SQL Server - it should be done in a reporting layer.

6

Here is a translation to English:

select Firstname, Lastname, Start_date, End_date, No_days
from Employee join Absence as Absence on absence.employee = employee.id
where Firstname = 'Susanne' and Start_date >'2020-01-24'

This returns 3 rows because Susanne was absent 3 times = 8 days, 8 days and 1 day. I would like to sum the No of days.

;

7

Where do you want to see that sum? It isn't clear what you mean by 'merge' the results - please explain how you want to the results to be returned.

8

I wish to get the sum of the No of days (17) included in my query. I have tried 'count' and 'group by' but that did not work.

This is the result of the query:

|Firstname|Lastname|Start_date|End_date|No_Days|
|Susanne|Olsen|2020-07-01|2020-07-08|8|
|Susanne|Olsen|2020-10-12|2020-10-19|8|
|Susanne|Olsen|2021-01-22|2021-01-22|1|

9

Again - where and how do you want to see the additional information?

Do you want this:

|Firstname|Lastname|Start_date|End_date|No_Days|Total
|Susanne|Olsen|2020-07-01|2020-07-08|8|8
|Susanne|Olsen|2020-10-12|2020-10-19|8|16
|Susanne|Olsen|2021-01-22|2021-01-22|1|17

Or do you want this:

|Firstname|Lastname|Start_date|End_date|No_Days|
|Susanne|Olsen|2020-07-01|2020-07-08|8|
|Susanne|Olsen|2020-10-12|2020-10-19|8|
|Susanne|Olsen|2021-01-22|2021-01-22|1|
|||||17|

Or is it this:

|Firstname|Lastname|Start_date|End_date|No_Days|Total
|Susanne|Olsen|2020-07-01|2020-07-08|8|17
|Susanne|Olsen|2020-10-12|2020-10-19|8|17
|Susanne|Olsen|2021-01-22|2021-01-22|1|17

Or something else?

Please help us help you...

10

Thanks! A total as in your middle suggestion. Will you please let me know, how you did that?

11

That is what I thought you were asking for - and it isn't something that should be done in SQL. A totals line is part of a report and should be done in the presentation layer.

However - you could use Grouping Sets to get something close to what you are looking for:

Declare @testTable Table (Fornavn varchar(35), Efternavn varchar(35), Start_dato date, Slut_dato date, Antal_dage int);
 Insert Into @testTable (Fornavn, Efternavn, Start_dato, Slut_dato, Antal_dage)
 Values ('Susanne', 'Olsen', '2020-07-01', '2020-07-08', 8)
      , ('Susanne', 'Olsen', '2020-10-12', '2020-10-19', 8)
      , ('Susanne', 'Olsen', '2021-01-22', '2021-01-22', 1)
      , ('Susanne', 'Jackson', '2020-07-03', '2020-07-08', 6)
      , ('Susanne', 'Jackson', '2020-10-14', '2020-10-18', 5)
      , ('Susanne', 'Jackson', '2021-01-20', '2021-01-21', 2);

 Select tt.Fornavn
      , tt.Efternavn
      , tt.Start_dato
      , tt.Slut_dato
      , sum(tt.Antal_dage) As Antal_dage
      , Antal_dage_2 = sum(datediff(day, tt.Start_dato, tt.Slut_dato) + 1)
   From @testTable                      tt
  Where tt.Fornavn = 'Susanne'
    And tt.Start_dato > '2020-01-24'
  Group By Grouping Sets (
        (tt.Fornavn, tt.Efternavn)
      , (tt.Fornavn, tt.Efternavn, tt.Start_dato, tt.Slut_dato)
        );

I added an additional person to the test table to show the results when you have more than a single person selected. You will get the first name (Fornavn) and last name (Efternavn) included in the results but I think that is much more clear than having blank values.

I also added a new column that calculates the number of days from the start date (Start_dato) and end date (Slut_dato) to show how you can get that value without having to pre-calculate the number of days (Antal_dage).

Note: in the grouping sets - the first set (tt.Fornavn, tt.Efternavn) gives you the totals by name and the second set ((tt.Fornavn, tt.Efternavn, tt.Start_dato, tt.Slut_dato) is required to include Start_dato and Slut_dato in the grouping.

1 Like