CODEWITHSUNDEEP
sql-server
Ben
Ben

Reputation: 609

Multiple SELECTS into single result row

I'm trying to combine 2 select statements into a single query, if possible I want to end up with 1 row containing 4 columns (2 from each query). I was sure i'd used something like the below in the past without issues, but I'm getting the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Thanks

SELECT 
(SELECT
SUM(SO.new_profits_sales_totalactualprofit) as TAP_AFFINITY, 
SUM(SO.new_profits_sales_totaldealprofit) as TDP_AFFINITY
FROM new_dealsheet DS
LEFT JOIN salesorder SO ON DS.new_dsheetid = SO.salesorderid
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId 
WHERE CAST(SO.New_purchaseordersenddate as date) = CAST(GETDATE() as date)  
AND SO.New_PassedToAdmin = 1
AND SP.New_SalesGroupIdName = 'AFFINITY') as t1,

(SELECT
SUM(SO.new_profits_sales_totalactualprofit) as TAP_GENUS, 
SUM(SO.new_profits_sales_totaldealprofit) as TDP_GENUS
FROM new_dealsheet DS
LEFT JOIN salesorder SO ON DS.new_dsheetid = SO.salesorderid
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId 
WHERE CAST(SO.New_purchaseordersenddate as date) = CAST(GETDATE() as date)  
AND SO.New_PassedToAdmin = 1
AND SP.New_SalesGroupIdName = 'GENUS') as t2

Upvotes: 0

Views: 39

Answers (1)

Martin Smith
Martin Smith

Reputation: 453887

You can cross join two single row resultsets to combine them.

WITH t1 AS
(
SELECT
SUM(SO.new_profits_sales_totalactualprofit) as TAP_AFFINITY, 
SUM(SO.new_profits_sales_totaldealprofit) as TDP_AFFINITY
FROM new_dealsheet DS
LEFT JOIN salesorder SO ON DS.new_dsheetid = SO.salesorderid
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId 
WHERE CAST(SO.New_purchaseordersenddate as date) = CAST(GETDATE() as date)  
AND SO.New_PassedToAdmin = 1
AND SP.New_SalesGroupIdName = 'AFFINITY'
), t2 AS
(
SELECT
SUM(SO.new_profits_sales_totalactualprofit) as TAP_GENUS, 
SUM(SO.new_profits_sales_totaldealprofit) as TDP_GENUS
FROM new_dealsheet DS
LEFT JOIN salesorder SO ON DS.new_dsheetid = SO.salesorderid
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId 
WHERE CAST(SO.New_purchaseordersenddate as date) = CAST(GETDATE() as date)  
AND SO.New_PassedToAdmin = 1
AND SP.New_SalesGroupIdName = 'GENUS'
)
SELECT *
FROM t1 cross join T2

But a better way would be to do it all in one query

SELECT SUM(CASE WHEN SP.New_SalesGroupIdName = 'GENUS' THEN SO.new_profits_sales_totalactualprofit END) AS TAP_GENUS,
       SUM(CASE WHEN SP.New_SalesGroupIdName = 'GENUS' THEN SO.new_profits_sales_totaldealprofit END)   AS TDP_GENUS,
       SUM(CASE WHEN SP.New_SalesGroupIdName = 'AFFINITY' THEN SO.new_profits_sales_totalactualprofit END) AS TAP_AFFINITY,
       SUM(CASE WHEN SP.New_SalesGroupIdName = 'AFFINITY' THEN SO.new_profits_sales_totaldealprofit END)   AS TDP_AFFINITY
        FROM   new_dealsheet DS
               LEFT JOIN salesorder SO
                      ON DS.new_dsheetid = SO.salesorderid
               LEFT JOIN New_salespeople SP
                      ON DS.New_SalespersonId = SP.New_salespeopleId
        WHERE  CAST(SO.New_purchaseordersenddate AS DATE) = CAST(GETDATE() AS DATE)
               AND SO.New_PassedToAdmin = 1
               AND SP.New_SalesGroupIdName IN ( 'GENUS','AFFINITY')

Upvotes: 2

Related Questions