none
Flattening the results of a query newbie question

    Question

  • Hi all,

    I have this query

    SELECT     commbyrepID, agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], [comm-intro-amount], 
                          [comm-intro-precentage], [exp-settle-date], Listers, Managers, Sellers, initials, role
    FROM         dbo.precalc_CommissionBySalesRep

    It returns the info as follows

    The only columns that are different in the record are the Listers Managers and Sellers columns and the role column (which isn't needed) and also the [comm-intro-percentage] and [comm-intro amount] rows which I need to sum up with the SUM() command i presume.

    I need each property per agent to be returned on a seperate row with the Listers Managers and Sellers all pushed into the one record so all those nulls are not there. (does that make sense?) Is it even possible? Thanks


    • Edited by Mordt Thursday, March 01, 2012 12:19 AM
    Thursday, March 01, 2012 12:18 AM

Answers

  • SELECT     
    	commbyrepID
    	,agent
    	,office
    	,address
    	,[property-type]
    	,[listing-source]
    	,[sale-status]
    	,[sale-date]
    	,[sale-price]
    	,[gross-comm]
    	,Sum([comm-intro-amount]) As [comm-intro-amount]
    	,Sum([comm-intro-precentage]) As [comm-intro-precentage]
    	,[exp-settle-date]
    	,Max(IsNull(Listers, '')) As Listers
    	,Max(IsNull(Managers, '')) As Managers
    	,Max(IsNull(Sellers, '')) As Sellers 
    	,initials
    	--,role
    FROM         
    	dbo.precalc_CommissionBySalesRep
    Group By 
    	commbyrepID
    	,agent
    	,office
    	,address
    	,[property-type]
    	,[listing-source]
    	,[sale-status]
    	,[sale-date]
    	,[sale-price]
    	,[gross-comm]
    	,[exp-settle-date]
    	,initials



    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    • Edited by Arbi Baghdanian Thursday, March 01, 2012 12:38 AM
    • Marked as answer by Mordt Thursday, March 01, 2012 12:59 AM
    • Unmarked as answer by Mordt Thursday, March 01, 2012 1:25 AM
    • Marked as answer by Mordt Thursday, March 01, 2012 1:26 AM
    Thursday, March 01, 2012 12:37 AM

All replies

  • SELECT     
    	commbyrepID
    	,agent
    	,office
    	,address
    	,[property-type]
    	,[listing-source]
    	,[sale-status]
    	,[sale-date]
    	,[sale-price]
    	,[gross-comm]
    	,Sum([comm-intro-amount]) As [comm-intro-amount]
    	,Sum([comm-intro-precentage]) As [comm-intro-precentage]
    	,[exp-settle-date]
    	,Max(IsNull(Listers, '')) As Listers
    	,Max(IsNull(Managers, '')) As Managers
    	,Max(IsNull(Sellers, '')) As Sellers 
    	,initials
    	--,role
    FROM         
    	dbo.precalc_CommissionBySalesRep
    Group By 
    	commbyrepID
    	,agent
    	,office
    	,address
    	,[property-type]
    	,[listing-source]
    	,[sale-status]
    	,[sale-date]
    	,[sale-price]
    	,[gross-comm]
    	,[exp-settle-date]
    	,initials



    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    • Edited by Arbi Baghdanian Thursday, March 01, 2012 12:38 AM
    • Marked as answer by Mordt Thursday, March 01, 2012 12:59 AM
    • Unmarked as answer by Mordt Thursday, March 01, 2012 1:25 AM
    • Marked as answer by Mordt Thursday, March 01, 2012 1:26 AM
    Thursday, March 01, 2012 12:37 AM
  • Hi Arbi,

    That's the way I thought at first but that doesn't work, all that does is remove the NULL's and leave blanks it doesn't flatten as expected under group by.

    Thanks

    Thursday, March 01, 2012 12:55 AM
  • I removed the id field and it works a charm :)

    thanks

    Thursday, March 01, 2012 1:00 AM
  • Ah i actually see an unforeseen issue with this and that if the property exists twice because two different agents are involved. the lsiter, manager and seller fields have empty values, where i want them to have the value of all the list manage and sell combined regardless of agent (basically there will be no blank spots anywhere. 

    So my question would it be more efficient to just create a new table with the lister manager and seller sections and join this table and the new table up

    or add selects for each of the Lister Manager and Seller Sections so I can get all the data for that field for that property regardless of agent.

    • Edited by Mordt Thursday, March 01, 2012 1:25 AM
    Thursday, March 01, 2012 1:25 AM
  • Mordt, 
    I will personally will go with new table and then join it. But it depends on your UI. 

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Thursday, March 01, 2012 1:43 AM
  • Thanks for your help Arbi.

    How would I join these two selects together ? The [address] and [sale-date] fields are unique to the record in both results

    Query 1

    SELECT agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], SUM([comm-intro-amount]) AS [comm-intro-amount], SUM([comm-intro-precentage]) AS [comm-intro-precentage], [exp-settle-date]
    FROM  precalc_CommissionBySalesRep
    GROUP BY agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], [exp-settle-date], initials

    Query 2

    SELECT     address, [sale-date], MAX(ISNULL(Listers, '')) AS Listers, MAX(ISNULL(Managers, '')) AS Managers, MAX(ISNULL(Sellers, '')) AS Sellers
    FROM         precalc_CommissionBySalesRep
    GROUP BY address, [sale-date], [sale-price]

    Thursday, March 01, 2012 2:33 AM
  • I think i have figured it out

    does this look right or Ok?

    SELECT *
    
    FROM
    (
    
    SELECT agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], SUM([comm-intro-amount]) AS [comm-intro-amount], SUM([comm-intro-precentage]) AS [comm-intro-precentage], [exp-settle-date]
    FROM  precalc_CommissionBySalesRep
    GROUP BY agent, office, address, [property-type], [listing-source], [sale-status], [sale-date], [sale-price], [gross-comm], [exp-settle-date], initials) as t1 LEFT OUTER JOIN
    
    (
    SELECT     address, [sale-date], MAX(ISNULL(Listers, '')) AS Listers, MAX(ISNULL(Managers, '')) AS Managers, MAX(ISNULL(Sellers, '')) AS Sellers
    FROM         precalc_CommissionBySalesRep
    GROUP BY address, [sale-date], [sale-price]) as t2 ON t1.address = t2.address
    

    Thursday, March 01, 2012 2:37 AM