group by on a transaction at line item level

Hi,

I need to group by transactions at line item level. As the each item in the transaction may belong to different category and I'musing case statement to identify particular category for one column , I'm using sub-query to retrieve the results at item level. Here is the code:

SELECT
fs.TransactionId
,fs.DateKey
,dc.FirstName
,dc.LastName
,dc.Company 
,dc.City
,CASE 
WHEN dp.Category = 'A'
THEN SUM(fs.ActualUnitPrice * fs.Quantity)
ELSE 0
END AS CategoryAPurchase
,sum(fs.ActualUnitPrice * fs.Quantity) AS PurchasesTotal 
FROM [factSales] fs(NOLOCK)
JOIN dimDate dd(NOLOCK) ON dd.DateKey = fs.Datekey
JOIN [dimProduct] dp(NOLOCK) ON fs.productkey = dp.productkey
JOIN [dimCustomer] dc(NOLOCK) ON fs.Custkey = dc.Custkey
where fs.Datekey=20150518 
GROUP BY TransactionId
,fs.Datekey
,dc.CustomerId
,dc.AccountNumber
,dc.FirstName
,dc.LastName
,dc.Company
,dc.City

Due to case by statement and having multiple category I have to use multiple group by. I was wondering is there a best approach make this query better?

select TransactionId
,DateKey
,FirstName
,LastName
,Company
,City
,Sum(CategoryAPurchase) CategoryAPurchase
,Sum(PurchasesTotal) As PurchasesTotal 
from
(
SELECT
fs.TransactionId
,fs.DateKey
,dc.FirstName
,dc.LastName
,dc.Company 
,dc.City
,CASE 
WHEN dp.Category = 'A'
THEN SUM(fs.ActualUnitPrice * fs.Quantity)
ELSE 0
END AS CategoryAPurchase
,sum(fs.ActualUnitPrice * fs.Quantity) AS PurchasesTotal 
FROM [factSales] fs(NOLOCK)
JOIN dimDate dd(NOLOCK) ON dd.DateKey = fs.Datekey
JOIN [dimProduct] dp(NOLOCK) ON fs.productkey = dp.productkey
JOIN [dimCustomer] dc(NOLOCK) ON fs.Custkey = dc.Custkey
where fs.Datekey=20150518 
GROUP BY TransactionId
,fs.Datekey
,dc.CustomerId
,dc.AccountNumber
,dc.FirstName
,dc.LastName
,dc.Company
,dc.City
)a
group by 
TransactionId
,Datekey
,FirstName
,LastName
,company
,City
ORDER BY TransactionId

Thanks!!

May 20th, 2015 12:56am

FYI: requirement is to get category A purchases as well as total purchase for that transaction.  
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 1:07am

Hi SQL_Admirer,

Could you please post the table DDL with sample data and expected output??

May 20th, 2015 2:49am

>> need to group by transactions at line item level. AS the each item in the transaction may belong to different product_category and I'm using case statement to identify particular product_category for one column, I'm using sub-query to retrieve the results at item level. <<  

SQL has a CASE expression, not a CASE statement. The WHEN clauses have to be at the same level of aggregation to work.  

There is no such thing as a generic category, generic sale_qty, etc.

Why did you fail to post any DDL? Why did you violation the ISO-11179 rules? Why do you think that the meta data _key (how a data element is used in one table) makes sense? Your date_key is absurd; a date is a unit of measurement, so it has to be a  <something in particular>_date to be valid. Are there also pounds and voltage keys? We use the term Calendar for a set of dates. Try this repair job: 

SELECT S.transaction_id, S.sale_date, C.cust_first_name, C.cust_last_name,
       C.compnay_duns, C.city_name, 
       SUM (CASE WHEN P.product_category = 'a'
       THEN(S.actual_unit_price * S.sale_qty)
       ELSE 0.00 END )
       AS product_category_a_sale, 
       SUM(S.actual_unit_price * S.sale_qty) AS sale_total
  FROM Sales AS S
       Products AS P,
       Customers AS 
 WHERE S.product_gtin = P.product_gtin
   AND S.customer_id = C.customer_id 
   AND S.sale_date = '2015-05-18' 
 GROUP BY S.transaction_id, S.sale_date, C.cust_first_name, C.cust_last_name, C.compnay_duns, C.city_name;
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 3:23am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics