Combing records question

1

I have the following statement

Select CONCAT(DIMEmployee.lastName, DIMEmployee.FirstName) as "Employee Name",SUM(DIMProduct.PRICe)as "Total $",count(dimproduct.price) as 'Number sold' ,
CONCAT(DIMCustomer.lastName, DIMCUstomer.FirstName) as "Cust Name",DIMTransaction.Type,MONTH(Fastactivity.Date) as 'Month Sold'

from Fastactivity
inner join DIMProduct on Fastactivity.DIMProductKey=DIMProduct.DIMProductKey
inner join DIMEmployee on Fastactivity.DIMEmployeeKey=DIMEmployee.DimEmployeeKey
inner join DIMCustomer on Fastactivity.CustomerKey=DIMcustomer.DIMCustomerKey
inner join DIMTransaction on Fastactivity.TransactionKey = DIMTransaction.DimTransactionKey

Group by DIMProduct.Price, Fastactivity.Date,DIMEmployee.LastName,DIMEmployee.FirstName,dimcustomer.LastName,DIMCUstomer.FirstName,DIMTransaction.Type

Which Returns

Capture

what command do I need to use to combine when the Employee name and customer name are the same for total $

2

this is microsoft tsql forum ..

in T-SQL we use sum command

select 
        employee_name 
      , sum(Total $)
from 
      table_name
group by 
    employee_name

what this will do ..

image

3

First thing - let's restructure the query so we can read it

 Select concat(DIMEmployee.lastName, DIMEmployee.FirstName) As "Employee Name"
      , sum(DIMProduct.PRICe) As "Total $"
      , count(dimproduct.price) As 'Number sold'
      , concat(DIMCustomer.lastName, DIMCUstomer.FirstName) As "Cust Name"
      , DIMTransaction.Type
      , month(Fastactivity.Date) As 'Month Sold'
   From Fastactivity
  Inner Join DIMProduct On Fastactivity.DIMProductKey = DIMProduct.DIMProductKey
  Inner Join DIMEmployee On Fastactivity.DIMEmployeeKey = DIMEmployee.DimEmployeeKey
  Inner Join DIMCustomer On Fastactivity.CustomerKey = DIMcustomer.DIMCustomerKey
  Inner Join DIMTransaction On Fastactivity.TransactionKey = DIMTransaction.DimTransactionKey
  Group By
        DIMProduct.Price
      , Fastactivity.Date
      , DIMEmployee.LastName
      , DIMEmployee.FirstName
      , dimcustomer.LastName
      , DIMCUstomer.FirstName
      , DIMTransaction.Type;

Now - we can see that you are grouping on all of the columns. What you want to do is group on the non-aggregated columns included in the SELECT. The goal is to aggregate the values based on the unique values in the grouping columns.

 Select concat(DIMEmployee.lastName, DIMEmployee.FirstName) As "Employee Name"
      , sum(DIMProduct.PRICe) As "Total $"
      , count(dimproduct.price) As 'Number sold'
      , concat(DIMCustomer.lastName, DIMCUstomer.FirstName) As "Cust Name"
      , DIMTransaction.Type
      , month(Fastactivity.Date) As 'Month Sold'
   From Fastactivity
  Inner Join DIMProduct On Fastactivity.DIMProductKey = DIMProduct.DIMProductKey
  Inner Join DIMEmployee On Fastactivity.DIMEmployeeKey = DIMEmployee.DimEmployeeKey
  Inner Join DIMCustomer On Fastactivity.CustomerKey = DIMcustomer.DIMCustomerKey
  Inner Join DIMTransaction On Fastactivity.TransactionKey = DIMTransaction.DimTransactionKey
  Group By
        Fastactivity.Date
      , DIMEmployee.LastName
      , DIMEmployee.FirstName
      , dimcustomer.LastName
      , DIMCUstomer.FirstName
      , DIMTransaction.Type;

But - that still won't work correctly - because you are summing and counting the price into the month of the activity date.

 Select concat(DIMEmployee.lastName, DIMEmployee.FirstName) As "Employee Name"
      , sum(DIMProduct.PRICe) As "Total $"
      , count(dimproduct.price) As 'Number sold'
      , concat(DIMCustomer.lastName, DIMCUstomer.FirstName) As "Cust Name"
      , DIMTransaction.Type
      , month(Fastactivity.Date) As 'Month Sold'
   From Fastactivity
  Inner Join DIMProduct On Fastactivity.DIMProductKey = DIMProduct.DIMProductKey
  Inner Join DIMEmployee On Fastactivity.DIMEmployeeKey = DIMEmployee.DimEmployeeKey
  Inner Join DIMCustomer On Fastactivity.CustomerKey = DIMcustomer.DIMCustomerKey
  Inner Join DIMTransaction On Fastactivity.TransactionKey = DIMTransaction.DimTransactionKey
  Group By
        month(Fastactivity.Date)
      , DIMEmployee.LastName
      , DIMEmployee.FirstName
      , dimcustomer.LastName
      , DIMCUstomer.FirstName
      , DIMTransaction.Type;

Although you can group by the individual columns - it is generally better to group by the expression used in the SELECT. So...

 Select concat(DIMEmployee.lastName, DIMEmployee.FirstName) As "Employee Name"
      , sum(DIMProduct.PRICe) As "Total $"
      , count(dimproduct.price) As 'Number sold'
      , concat(DIMCustomer.lastName, DIMCUstomer.FirstName) As "Cust Name"
      , DIMTransaction.Type
      , month(Fastactivity.Date) As 'Month Sold'
   From Fastactivity
  Inner Join DIMProduct On Fastactivity.DIMProductKey = DIMProduct.DIMProductKey
  Inner Join DIMEmployee On Fastactivity.DIMEmployeeKey = DIMEmployee.DimEmployeeKey
  Inner Join DIMCustomer On Fastactivity.CustomerKey = DIMcustomer.DIMCustomerKey
  Inner Join DIMTransaction On Fastactivity.TransactionKey = DIMTransaction.DimTransactionKey
  Group By
        month(Fastactivity.Date)
      , concat(DIMEmployee.lastName, DIMEmployee.FirstName)
      , concat(DIMCustomer.lastName, DIMCUstomer.FirstName)
      , DIMTransaction.Type;

And now - you should be able to adjust the grouping as needed.

1 Like