UNION and GROUP By

18 Aug 2006 6:50 PM

Ric

I want to write a UNION query and group by the entire result set:

select submitdate, emplid, sum(submithours)
from StandardTime
union
select submitdate, emplid,sum(submithours)
from NonStandardTime
group submitdate,emplid

This does not work, I need a group by on each individual select statement.
Is there a way to have a group by that applies to the combined result set? I
can do it using a temp table (or a view) but I was hoping that it could be
done in a single query.

Ric

18 Aug 2006 7:24 PM

SQL Ken

YOU CAN but your union i the subquery
somehting like thiswould do the job:

SELECT *
FROM
     (select submitdate, emplid, sum(submithours)
       from StandardTime
   union
   select submitdate, emplid,sum(submithours)
    from NonStandardTime)
   Mytable

GROUP BY submitdate,emplid

Ric wrote:
Show quote

> I want to write a UNION query and group by the entire result set:
>
> select submitdate, emplid, sum(submithours)
> from StandardTime
> union
> select submitdate, emplid,sum(submithours)
> from NonStandardTime
> group submitdate,emplid
>
> This does not work, I need a group by on each individual select statement.
> Is there a way to have a group by that applies to the combined result set? I
> can do it using a temp table (or a view) but I was hoping that it could be
> done in a single query.
>
> Ric

18 Aug 2006 7:30 PM

Ric

Thanks! That works for me. :)

Ric

Show quote

"SQL Ken" wrote:

> YOU CAN but your union i the subquery
> somehting like thiswould do the job:
>
> SELECT *
> FROM
>      (select submitdate, emplid, sum(submithours)
>        from StandardTime
>    union
>    select submitdate, emplid,sum(submithours)
>     from NonStandardTime)
>    Mytable
>
> GROUP BY submitdate,emplid
>
>
>
>
>
> Ric wrote:
> > I want to write a UNION query and group by the entire result set:
> >
> > select submitdate, emplid, sum(submithours)
> > from StandardTime
> > union
> > select submitdate, emplid,sum(submithours)
> > from NonStandardTime
> > group submitdate,emplid
> >
> > This does not work, I need a group by on each individual select statement.
> > Is there a way to have a group by that applies to the combined result set? I
> > can do it using a temp table (or a view) but I was hoping that it could be
> > done in a single query.
> >
> > Ric
>
>

18 Aug 2006 7:26 PM

Arnie Rowland

Here's a silly little example that should help you move in the right direction:

USE Northwind
GO

SELECT
     CustomerID
   , CompanyName
FROM ( SELECT
            c.CustomerID
          , c.CompanyName
       FROM Customers c
          JOIN Orders o
             ON c.CustomerID = o.CustomerID

       UNION ALL

       SELECT
            cast( e.EmployeeID AS varchar(10))
          , ( e.FirstName + ' ' + e.LastName )
       FROM Employees e
          JOIN Orders o
             ON e.EmployeeID = o.EmployeeID
      ) x
GROUP BY
     CustomerID
   , CompanyName

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Ric" <R**@discussions.microsoft.com> wrote in message news:3B6A48E6-CEED-4813-AE31-93654BB192AA@microsoft.com...
>I want to write a UNION query and group by the entire result set:
>
> select submitdate, emplid, sum(submithours)
> from StandardTime
> union
> select submitdate, emplid,sum(submithours)
> from NonStandardTime
> group submitdate,emplid
>
> This does not work, I need a group by on each individual select statement.
> Is there a way to have a group by that applies to the combined result set? I
> can do it using a temp table (or a view) but I was hoping that it could be
> done in a single query.
>
> Ric