Totaling 2 columns into a new column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Totaling 2 columns into a new column

Author

Topic

stevelalor
Starting Member

4 Posts

Posted - 2007-07-27 : 04:55:13

Hi all,

I'm trying to product a total of two columns but everything I try seems to not
work for me.

Here is a outline to my SQL so far.

SELECT
mo.ModelId,
NewSales = (Select COUNT(1) FROM tblDiaryEntries
WHERE ModelId = mo.ModelId AND New = 1 AND
RID = 1 AND
SPID = 3565),
UsedSales = (Select COUNT(1) FROM tblDiaryEntries
WHERE ModelId = mo.ModelId AND New = 0 AND
RID = 1 AND
SPID = 3565),
SUM(NewSales + UsedSales) AS Total
FROM tblModels mo

Everything works ok apart from my Total column, I have even tried to replace
the NewSales + UsedSales with the selects I used to get those two columns
orginally (Effectively performing the SELECT TWICE.
This produced a "Cannot perform an aggregate function on an expression
containing an aggregate or a subquery."

Please could someone point me in the right direction? I basically need to be
able to work out the total of the new and used sales columns and put this in
the third "Total" column.

Many thanks

Steve

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-07-27 : 05:46:45

You want the same total returned with all rows? or you want sum of NewSales and UsedSales of each row?

stevelalor
Starting Member

4 Posts

Posted - 2007-07-27 : 06:01:57

Hi

I would like the total per row.

Thanks

Steve

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 07:38:20

[code]SELECT m.ModelID,
SUM(CASE WHEN de.New = 1 THEN 1 ELSE 0 END) AS NewSales,
SUM(CASE WHEN de.New = 0 THEN 1 ELSE 0 END) AS UsedSales
COUNT(de.New) AS Total
FROM tblModels AS m
LEFT JOIN tblDiaryEntries AS de ON de.ModelID = m.ModelID AND de.RID = 1 AND de.SPID = 3565
GROUP BY m.ModelID
ORDER BY m.ModelID[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources