CODEWITHSUNDEEP
sqlsql-serversql-server-2008
yoda
yoda

Reputation: 121

Can I use a GROUP BY in the first SELECT when CROSS APPLY is used further on?

Is it possible to use a GROUP BY in the first section of a statement when CROSS APPLY and OUTER APPLY are used further on in the query or do I need to add another CROSS APPLY?

Here is sample query with ** where I have tried to add a GROUP BY without success (as I can't load the real one):

select acc.custaccount, acc.product, acc.invoicedate
from account acc
join title t
on acc.custaccount = t.custaccount
***group by here***
Cross apply (select number, date
from invoice inv where 
custaccount = acc.custaccount
group by number, date) inv

left join invoicedetail invdet
on inv.number = invdet.number

out apply(select invdet.number, invdet.units, invdet.value
group by invdet.number, invdet.units, invdet.value)
from

etc.....

Upvotes: 2

Views: 541

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

I'd break a long complex query in simpler parts using Common-Table Expressions (CTE). See also WITH.

Something like this:

WITH
CTE1
AS
(
    select acc.custaccount, acc.product, acc.invoicedate
    from 
        account acc
        join title t on acc.custaccount = t.custaccount
    ***group by here***
)
,CTE2
AS
(
    SELECT ...
    FROM
        CTE1
        Cross apply 
        (
            select number, date
            from invoice inv 
            where inv.custaccount = CTE1.custaccount
            group by number, date
        ) AS A
)
SELECT ...
FROM 
    CTE2
    left join invoicedetail invdet on CTE2.number = invdet.number
    outer apply
    (
        select invdet.number, invdet.units, invdet.value
        FROM ...
        WHERE ...
        group by invdet.number, invdet.units, invdet.value
    ) AS A
;

Just build the query step-by-step and examine result of each intermediate CTE.

First step:

With 
CTE1 
as 
( 
    select fe.snap_name,fe.snap_accountid, fe.snap_entitlementcategory, fe.snap_entitlementcode, fi.invoicenumber, fi.totalamount 
    from 
        FilteredInvoice fi 
        join FilteredSNAP_entitlement fe on fi.accountid = fe.snap_accountid 
    group by fe.snap_name,fe.snap_accountid, fe.snap_entitlementcategory, fe.snap_entitlementcode, fi.invoicenumber, fi.totalamount 
)
SELECT *
FROM CTE1
;

Second step:

With 
CTE1 
as 
( 
    select fe.snap_name,fe.snap_accountid, fe.snap_entitlementcategory, fe.snap_entitlementcode, fi.invoicenumber, fi.totalamount 
    from 
        FilteredInvoice fi 
        join FilteredSNAP_entitlement fe on fi.accountid = fe.snap_accountid 
    group by fe.snap_name,fe.snap_accountid, fe.snap_entitlementcategory, fe.snap_entitlementcode, fi.invoicenumber, fi.totalamount 
)
SELECT *
FROM
    CTE1
    CROSS APPLY
    (
        select number, date
        from invoice inv 
        where inv.custaccount = CTE1.custaccount
        group by number, date
    ) AS A
;

Third step:

With 
CTE1 
as 
( 
    select fe.snap_name,fe.snap_accountid, fe.snap_entitlementcategory, fe.snap_entitlementcode, fi.invoicenumber, fi.totalamount 
    from 
        FilteredInvoice fi 
        join FilteredSNAP_entitlement fe on fi.accountid = fe.snap_accountid 
    group by fe.snap_name,fe.snap_accountid, fe.snap_entitlementcategory, fe.snap_entitlementcode, fi.invoicenumber, fi.totalamount 
)
,CTE2
AS
(
    SELECT *
    FROM
        CTE1
        CROSS APPLY
        (
            select number, date
            from invoice inv 
            where inv.custaccount = CTE1.custaccount
            group by number, date
        ) AS A
)
SELECT *
FROM CTE2
;

Upvotes: 2

Related Questions