T-SQL: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Look up CTE (common table expressions), it's usually a much cleaner way vs nested select in select statements.

Personally I didn't learn from a book, I. Just started projects and learned as I went. Often there are multiple ways to do a thing, so rewriting just to try a different way is great experience.

Some general rules:
RBAR (row by agonizing row) is bad. Think in columns, not rows. Running totals were one of the only things that required cursor but there are better ways in sql 2016.
Index anything you are using in a where clause
Changing the order of things can make a big difference, selecting records in a date range before doing other things limits the result set and can drastically speed things up.
Learn about levels of normalization. There is no one right answer for all situations.
Pick a naming convention without spaces, I like lower camel.

Basically, in simplified form, this is what you are trying to do:

select
(       (select TotalSales, Collector from Table1) + (select TotalSales, Collector from Table2)        )  / 360


So you are tring to add one pair (totalsales, collector) to another pair (totalsales, collector). There's no way to do it.

You can add one number to another one number:

select
(       (select totalsales from table1) + (select totalsales from table2)    )    / 360


From the look of your query, I don't see why you have two subqueries. If I'm not mistaken, you can have one with RM20101.RMDTYPAL IN (1, 3, 5, 7, 8)

Hello:

I was able to do this with a couple of cte's, as shown in my code below.  Thank you!

But, I also need to divide the resulting data by 360.  What syntax can I use, in order to do so?

Also, I have another set of code (not shown) which is a "regular" select statement.  I'm going to want to divide the results of these two cte's divided by 360 "into" the results of that regular select statement.  How do I do that?

Thanks!

with cte1
as
(
(select Sum(RM20101.ORTRXAMT) as [Total Sales], CN00500.CRDTMGR as [Collector]
                         FROM  RM20101 INNER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                         WHERE  (RM20101.DOCDATE >= '2015-12-27'
                                  AND RM20101.DOCDATE <= '2016-12-20'
                                  AND RM20101.RMDTYPAL IN (1, 3, 5)
                                  AND RM20101.VOIDSTTS = 0
                                  AND CN00500.CRDTMGR <> '')
                         GROUP BY CN00500.CRDTMGR)),
cte2
as
(
(select Sum(RM20101.ORTRXAMT) as [Total Sales], CN00500.CRDTMGR as [Collector]
                         FROM  RM20101 INNER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                         WHERE  (RM20101.DOCDATE >= '2015-12-27'
                                  AND RM20101.DOCDATE <= '2016-12-20'
                                  AND RM20101.RMDTYPAL IN (7, 8)
                                  AND RM20101.VOIDSTTS = 0
                                  AND CN00500.CRDTMGR <> '')
                         GROUP BY CN00500.CRDTMGR))
(select cte1.Collector, (cte1.[Total Sales] + cte2.[Total Sales]) as [Total Sales]
from cte1
INNER JOIN cte2 on cte1.Collector = cte2.Collector) 

Select allOpen in new window

Have you run my proposal solution? It does the division analog to your first post, it only divides the second value..

Hello:

Per the code shown below, I'm very close to having this wrapped up.

But, I still need to know how to divide "Outstanding Sales" by "Average Sales Per Day".

I have tried to do this within the code below.  But, every time I try, I have to use a parenthes symbol.  SQL never likes it when I do that.  Such instances represent 25% of my postings here on experts-exchange.  :)

Please help.  Thank you!

John

with cte 
as
(
SELECT CN00500.CRDTMGR AS [Collector], SUM(RM00103.CUSTBLNC) as [Outstanding Sales]
                        FROM RM00103 INNER JOIN CN00500 ON RM00103.CUSTNMBR = CN00500.CUSTNMBR
						WHERE CN00500.CRDTMGR <> '' GROUP BY CN00500.CRDTMGR
						HAVING SUM(RM00103.CUSTBLNC) <> 0),
cte1
as
(
(select Sum(RM20101.ORTRXAMT) as [Total Sales], CN00500.CRDTMGR as [Collector]
                         FROM  RM20101 INNER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                         WHERE  (RM20101.DOCDATE >= '2015-12-27'
                                  AND RM20101.DOCDATE <= '2016-12-20'
                                  AND RM20101.RMDTYPAL IN (1, 3, 5)
                                  AND RM20101.VOIDSTTS = 0
                                  AND CN00500.CRDTMGR <> '')
                         GROUP BY CN00500.CRDTMGR)),
cte2
as
(
(select Sum(RM20101.ORTRXAMT) as [Total Sales], CN00500.CRDTMGR as [Collector]
                         FROM  RM20101 INNER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                         WHERE  (RM20101.DOCDATE >= '2015-12-27'
                                  AND RM20101.DOCDATE <= '2016-12-20'
                                  AND RM20101.RMDTYPAL IN (7, 8)
                                  AND RM20101.VOIDSTTS = 0
                                  AND CN00500.CRDTMGR <> '')
                         GROUP BY CN00500.CRDTMGR))
select cte.Collector, 
cte.[Outstanding Sales], (cte1.[Total Sales] + cte2.[Total Sales]) / 360 as [Average Sales Per Day]
from cte	
INNER JOIN cte1 on cte.Collector = cte1.Collector 
INNER JOIN cte2 on cte1.Collector = cte2.Collector 

Select allOpen in new window

Thank you, to all who helped me!  I really appreciate it.

I figured out my last question, on my own!  :)  Yay!

Anyway, below is the finished product, which calculates Days Sales Outstanding (DSO) by Collector (i.e. Credit Manager).

Thanks, again!

John

with cte 
as
(
SELECT CN00500.CRDTMGR AS [Collector], 
SUM(RM00103.CUSTBLNC) as [Sales Outstanding]
                        FROM RM00103 INNER JOIN CN00500 ON RM00103.CUSTNMBR = CN00500.CUSTNMBR
						WHERE CN00500.CRDTMGR <> '' GROUP BY CN00500.CRDTMGR
						HAVING SUM(RM00103.CUSTBLNC) <> 0),
cte1
as
(
(select Sum(RM20101.ORTRXAMT) as [Total Sales], 
CN00500.CRDTMGR as [Collector]
                         FROM  RM20101 INNER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                         WHERE  (RM20101.DOCDATE >= '2015-12-27'
                                  AND RM20101.DOCDATE <= '2016-12-20'
                                  AND RM20101.RMDTYPAL IN (1, 3, 5)
                                  AND RM20101.VOIDSTTS = 0
                                  AND CN00500.CRDTMGR <> '')
                         GROUP BY CN00500.CRDTMGR)),
cte2
as
(
(select Sum(RM20101.ORTRXAMT) as [Total Sales], 
CN00500.CRDTMGR as [Collector]
                         FROM  RM20101 INNER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
                         WHERE  (RM20101.DOCDATE >= '2015-12-27'
                                  AND RM20101.DOCDATE <= '2016-12-20'
                                  AND RM20101.RMDTYPAL IN (7, 8)
                                  AND RM20101.VOIDSTTS = 0
                                  AND CN00500.CRDTMGR <> '')
                         GROUP BY CN00500.CRDTMGR))
--select cte.Collector, 
--(cte1.[Total Sales] + cte2.[Total Sales]) / 360 as [Average Sales Per Day]
--from cte	
--INNER JOIN cte1 on cte.Collector = cte1.Collector 
--INNER JOIN cte2 on cte1.Collector = cte2.Collector 
--UNION
select cte.Collector, cte.[Sales Outstanding] / ((cte1.[Total Sales] + cte2.[Total Sales]) / 360) as [DSO]
from cte
INNER JOIN cte1 on cte.Collector = cte1.Collector 
INNER JOIN cte2 on cte1.Collector = cte2.Collector 

Select allOpen in new window