CODEWITHSUNDEEP
sql-servert-sql
freakostudent
freakostudent

Reputation: 115

TSQL - Return a percentage, when using a group without grouping on percentage columns

I've searched everywhere and cannot find what I'm looking for. I pull a data set into two temp tables broken down by day. In one of the selects off the temp tables I need to group by week. This causes a problem because it breaks my percentages. I would greatly appreciate any guidance on how I can get the below query to execute with the selected group by options.

Select datepart(wk,ts.TotalDate) AS Date,
       Tender_Type,
       SUM(TransactionsbyType),
       SUM(TotalDayTransactions),
       ROUND(TransactionsbyType * 100.0 / TotalDayTransactions, 3),
       ROUND(TenderAmountAuthorized * 100.0 / TotalDayAuthAmount, 3) AS 'PercentOfSalesByTenderType'
from  #TotalSalesByOrderingSystem tso
join #TotalSales ts on ts.TotalDate = tso.TotalOrderingSysDate 
group by  datepart(wk,ts.TotalDate), Tender_Type  
order by  datepart(wk,ts.TotalDate), Tender_Type

When executed query returns a failure stating that the columns used to get the percentage TransactionsbyType and TotalDayTransactions are not in the aggregate.

If I place them in the aggregate the data is not collapsed properly into weeks. I've previously tried the solutions here unsuccessfully: stackoverflow.com/questions/6207224/calculating-percentages-with-group-by-query

Upvotes: 0

Views: 108

Answers (1)

Nick
Nick

Reputation: 147166

If you want the percentages over the same period, you just need to use the same SUMs in those computations:

Select datepart(wk,ts.TotalDate) AS Date,
       Tender_Type,
       SUM(TransactionsbyType),
       SUM(TotalDayTransactions),
       ROUND(SUM(TransactionsbyType) * 100.0 / SUM(TotalDayTransactions), 3),
       ROUND(SUM(TenderAmountAuthorized) * 100.0 / SUM(TotalDayAuthAmount), 3) AS 'PercentOfSalesByTenderType'
from  #TotalSalesByOrderingSystem tso
join #TotalSales ts on ts.TotalDate = tso.TotalOrderingSysDate 
group by  datepart(wk,ts.TotalDate), Tender_Type  
order by  datepart(wk,ts.TotalDate), Tender_Type

Upvotes: 1

Related Questions