SQL with Group by need total for field

So I have a sql statement that breaks sales out by a few groups. This is then grouped by several fields.  I need to add one more field to this statement that is percentage of total of last year.  

Here is the SQL that I have working now.

SELECT     OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep,
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LAST FY Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END)AS 'YTD Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END) AS 'LYTD Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END) - SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END)as 'YTD VAR',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LFM Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LYFM Sales',	
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)-SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)as 'LFM VAR YOY'

FROM         OINV INNER JOIN
                      INV1 ON OINV.DocEntry = INV1.DocEntry INNER JOIN
                      INV12 ON OINV.DocEntry = INV12.DocEntry INNER JOIN
                      OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN
                      CRD1 ON OCRD.CardCode = CRD1.CardCode AND INV1.ShipToCode = CRD1.Address
GROUP BY OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep

Open in new window


This is the sql that with out other fields or group by gives me the total sales for last year.

SELECT SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 THEN INV1.Quantity * INV1.Price ELSE 0 END) AS 'Last FY Total'
                            FROM OINV INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry

Open in new window


I would like to add another calculated field that is the Last FY Sales / Last FY Total  I tried to do it in a derived table and just pass the total value but I could not get it to work.
notasgoodasyouAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
For now, I have place holder for %.
Please run this to see if it produces what you have now before adding the actual % column.
Select 'LAST FY Sales', 'YTD Sales', 'LYTD Sales', 'YTD VAR', 'LFM Sales', 'LYFM Sales', 'LFM VAR YOY', 1 + 2 As 'Percntage'
From
(
-- your existing code starts here
SELECT     OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep,
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LAST FY Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END)AS 'YTD Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END) AS 'LYTD Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END) - SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END)as 'YTD VAR',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LFM Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LYFM Sales',	
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)-SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)as 'LFM VAR YOY'

FROM         OINV INNER JOIN
                      INV1 ON OINV.DocEntry = INV1.DocEntry INNER JOIN
                      INV12 ON OINV.DocEntry = INV12.DocEntry INNER JOIN
                      OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN
                      CRD1 ON OCRD.CardCode = CRD1.CardCode AND INV1.ShipToCode = CRD1.Address
GROUP BY OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep
-- your existing code endss here
) As t

Open in new window


Mike
0
dsackerContract ERP Admin/ConsultantCommented:
I changed your field names to using brackets instead of single quotes, so that they could be referenced. I haven't tested this, so let me know if it works:

SELECT  OINV.CardName,
        INV12.CityS,
        INV12.StateS,
        CRD1.U_SIF_OSRep,
        SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END) AS [LAST FY Sales],
       (SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END) * 100) / [Last FY Total] AS [Last FY Sales %],
        SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END) AS [YTD Sales],
        SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END) AS [LYTD Sales],
        SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END) - SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END)as [YTD VAR],
        SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS [LFM Sales],
        SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS [LYFM Sales],	
        SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)-SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)as [LFM VAR YOY]
FROM    OINV
INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry
INNER JOIN INV12 ON OINV.DocEntry = INV12.DocEntry
INNER JOIN OCRD ON OINV.CardCode = OCRD.CardCode
INNER JOIN CRD1 ON OCRD.CardCode = CRD1.CardCode AND INV1.ShipToCode = CRD1.Address
INNER JOIN (SELECT DocEntry,
                   SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 THEN INV1.Quantity * INV1.Price ELSE 0 END) AS 'Last FY Total'
            FROM   OINV) LFYT
        ON  LFYT.DocEntry = OINV.DocEntry
GROUP BY OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep, [Last FY Total]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
re:> I tried to do it in a derived table and just pass the total value but I could not get it to work.

You can have either nested derived tables or two CTEs prior to the outer query if it is necessary.
0
notasgoodasyouAuthor Commented:
eghtebas

your solution just gave me fields that had no headers with the field names in the fields.  I have modified based on dsacker's comments and got it to work with the place holder.

Select [CardName], [CityS], [StateS], [U_SIF_OSRep],[LAST FY Sales], [YTD Sales], [LYTD Sales], [YTD VAR], [LFM Sales], [LYFM Sales], [LFM VAR YOY], 1 + 2 As 'Percntage'
From
(
-- your existing code starts here
SELECT     OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep,
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LAST FY Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END)AS 'YTD Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END) AS 'LYTD Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END) - SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END)as 'YTD VAR',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LFM Sales',
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LYFM Sales',	
					SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)-SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)as 'LFM VAR YOY'

FROM         OINV INNER JOIN
                      INV1 ON OINV.DocEntry = INV1.DocEntry INNER JOIN
                      INV12 ON OINV.DocEntry = INV12.DocEntry INNER JOIN
                      OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN
                      CRD1 ON OCRD.CardCode = CRD1.CardCode AND INV1.ShipToCode = CRD1.Address
GROUP BY OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep
-- your existing code endss here
) As t

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
notasgoodasyou, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.