How do i do a group by on multiple data Sets in SQL Server 2008

Question

I have a query that has 2 data sets that I have union'd together. I have done a group by on each data Set as I am working with 2 different product types that are in two different tables but have the same data columns in each.

I want to group the data set as a whole and nothing I try seems to be working.

Here below is the code I have so far

(SELECT Distinct 
    max(Incep) as Startdate, 
    q1.Client, 
    max(q1.Ref) as Reference, 
    Max(Title) as Title,
    Max(Forename)as Firstname, 
    Max(Surname) as Lastname, 
    max(Addr1) as Line1, 
    Max(Addr2)as line2, 
    max(Addr3) as line3, 
    MAX(addr4) as Line4,
    Max(a1.Postcode) as Postcode, 
    max(Abode) as Abodetype,  
    Max(Phone) as Phone,
    Max(fax) as Faxnumber, 
    max(Field1) as Marketing,
    Max(a1.Age) as Ageofclient, 
    Max(Test)Passeddate, 
    max(Resdate) as residencydate, 
    Max(Occup) as Occupation, 
    max(EOccup) as industry, 
    Max(RESTR) as DriverRestrictions, 
    Max(Cover) As Covertype,  
    MAx(BONUS) As NCBYears, 
    Max([USE]) as Classofuse, 
    max(REG) as VehicleReg, 
    max(ABI) as VehicleABI, 
    Max(BOUGHT) as PurchasedDate, 
    max(Seats) as VehicleSeats, 
    MAX(a2.YEAR) as Yearofmake, 
    Max(garage) as Overnightlocation 
FROM Quotes q1
    left join agents QZ on QZ.CDLCode =q1.Op collate SQL_Latin1_General_CP1_CI_AS
    left join MIS_O_C a1 on a1.Client = q1.client
    left join MIS_O_PPC a2 on a2.Client = q1.client and a2.Ref = Q1.Ref
WHERE EDate > GETDATE()-180
    and reportgroup not in ('Renewals', 'Underwriting', 'Exclude')
    and Occup not in ('Delivery Courier','Courier')
    and Field1 = 'CROSS'
    and SEATS > '8'
    and [USE] not in ('3')
    and Source in ('DIREC', '1A984', '1A997','1A982', '1A998') 
    and Ptype = 'PC'
GROUP BY q1.Client

UNION ALL

SELECT Distinct 
    max(Incep) as Startdate, 
    q1.Client, 
    max(q1.Ref) as Reference, 
    Max(Title) as Title,
    Max(Forename)as Firstname, 
    Max(Surname) as Lastname, 
    max(Addr1) as Line1, 
    Max(Addr2)as line2, 
    max(Addr3) as line3, 
    MAX(addr4) as Line4,
    Max(a1.Postcode) as Postcode, 
    max(Abode) as Abodetype,
    Max(Phone) as Phone,
    Max(fax) as Faxnumber, 
    max(Field1) as Marketing,  
    Max(a1.Age) as Ageofclient, 
    Max(Test)Passeddate, 
    max(Resdate) as residencydate, 
    Max(Occup) as Occupation, 
    max(EOccup) as industry, 
    Max(RESTR) as DriverRestrictions, 
    Max(Cover) As Covertype,  
    MAx(BONUS) As NCBYears, 
    Max([USE]) as Classofuse, 
    max(REG) as VehicleReg, 
    max(ABI) as VehicleABI, 
    Max(BOUGHT) as PurchasedDate, 
    max(Seats) as VehicleSeats, 
    MAX(a2.YEAR) as Yearofmake, 
    Max(garage) as Overnightlocation 
FROM Quotes q1
    left join agents QZ on QZ.CDLCode =q1.Op collate SQL_Latin1_General_CP1_CI_AS
    left join MIS_O_C a1 on a1.Client = q1.client
    left join MIS_O_PGV a2 on a2.Client = q1.client 
        and a2.Ref = Q1.Ref
WHERE EDate > GETDATE()-180
    and reportgroup not in ('Renewals', 'Underwriting', 'Exclude')
    and Occup not in ('Delivery Courier','Courier')
    and Field1 = 'CROSS'
    and SEATS > '8'
    and [USE] not in ('3')
    and Source in ('DIREC', '1A984', '1A997','1A982', '1A998') 
    and Ptype = 'GV'
GROUP BY q1.Client)

How do I group the two data sets as a whole?

Answer 1

Use the UNION ALL only for your product tables, not for all the query

Select Distinct max(Incep) as Startdate, q1.Client, max(q1.Ref) as Reference, Max(Title) as Title,Max(Forename)as Firstname, Max(Surname) as Lastname, max(Addr1) as Line1, Max(Addr2)as line2, max(Addr3) as line3, MAX(addr4) as Line4,Max(a1.Postcode) as Postcode, max(Abode) as Abodetype,  Max(Phone) as Phone,  Max(fax) as Faxnumber, max(Field1) as Marketing,  
    Max(a1.Age) as Ageofclient, Max(Test)Passeddate, max(Resdate) as residencydate, Max(Occup) as Occupation, max(EOccup) as industry, Max(RESTR) as DriverRestrictions, Max(Cover) As Covertype,  MAx(BONUS) As NCBYears, Max([USE]) as Classofuse, max(REG) as VehicleReg, max(ABI) as VehicleABI, Max(BOUGHT) as PurchasedDate, max(Seats) as VehicleSeats, MAX(a2.YEAR) as Yearofmake, Max(garage) as Overnightlocation from Quotes q1
    left join agents QZ on QZ.CDLCode =q1.Op collate SQL_Latin1_General_CP1_CI_AS
    left join MIS_O_C a1 on a1.Client = q1.client
    left join (
        SELECT * FROM MIS_O_PPC WHERE Ptype = 'PC'
        WHERE
        UNION ALL
        SELECT * FROM MIS_O_PGV WHERE Ptype = 'GV'
    ) a2 on a2.Client = q1.client and a2.Ref = Q1.Ref
    where EDate > GETDATE()-180
    and reportgroup not in ('Renewals', 'Underwriting', 'Exclude')
    and Occup not in ('Delivery Courier','Courier')
    and Field1 = 'CROSS'
    and SEATS > '8'
    and [USE] not in ('3')
    and Source in ('DIREC', '1A984', '1A997','1A982', '1A998') 
    Group by q1.Client

Answer 2

If you use your script as a subquery and GROUP BY outside then it will work;

    SELECT FieldNames
    FROM
    (   SELECT FieldNames
        FROM Table1
        GROUP BY FieldNames

        UNION

        SELECT FieldNames
        FROM Table2
        GROUP BY FieldNames
    ) a
    GROUP BY FieldNames