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
 General SQL Server Forums
 New to SQL Server Programming
 Summing up Values

Author  Topic 

akien_ghie09
Starting Member

22 Posts
Posted - 2011-12-20 : 21:12:45
How can I sum the values in a column when I'm using an alias?

How can I sum the total of the values in a row?

This is an example of my table:

Restaurant Code | Restaurant Name | 100 & BELOW | 101-200 | 201-300
001 | MORAYTA | 1 | 102 | 154
003 | GREENHILLS | 13 | 419 | 761
007 | UN AVENUE | 5 | 472 | 623
008 | QUEZON AVENUE | 3 | 271 | 541
009 | GREENBELT | 13 | 576 | 756

This is the result I wanted to show:

RestaurantCode | RestaurantName | 100&BELOW | 101-200 | 201-300 |
001 | MORAYTA | 1 | 102 | 154 | 257
003 | GREENHILLS | 13 | 419 | 761 | 1193
007 | UN AVENUE | 5 | 472 | 623 | 1100
008 | QUEZON AVENUE | 3 | 271 | 541 | 815
009 | GREENBELT | 13 | 576 | 756 | 1345
| 35 | 1840 | 2835 | 4710

Thanks in advance



AkiEn09

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2011-12-20 : 22:18:58
What version of SQL Server you are using ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akien_ghie09
Starting Member

22 Posts
Posted - 2011-12-20 : 22:40:50
Actually I'm using RazorSQL v. 5.6.2

What query I can use?

Thanks



AkiEn09
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2011-12-20 : 22:46:38
quote:
Originally posted by akien_ghie09

Actually I'm using RazorSQL v. 5.6.2

What query I can use?

Thanks



AkiEn09


I have no idea. SQLTeam.com is on Microsoft SQL Server. Perhaps you should be posting at their support forum

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akien_ghie09
Starting Member

22 Posts
Posted - 2011-12-20 : 23:04:52
Ahh.. Hmmm..

The Syntax is the same.
I'm just using a different application.

Anyways, hoq can I add it using Microsoft SQL Server?

I'm still hoping that it'll work in here.

Thanks.



AkiEn09
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2011-12-20 : 23:27:24
quote:
The Syntax is the same.

not necessary. Each DBMS has its own implementation that varies from others.

what you want is just a total line. This should be implemented in your front end application.

To do this in SQL,

select ....
from   yourtable
union all
select sum(col1), sum(col2), .. . 
from   yourtable


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akien_ghie09
Starting Member

22 Posts
Posted - 2011-12-21 : 00:15:03
Okay. I'll try to switch on Microsoft SQL Server.

I think It's much easier there.

Thanks anyways.



AkiEn09
Go to Top of Page

akien_ghie09
Starting Member

22 Posts
Posted - 2011-12-21 : 01:42:37
This is my code:

DECLARE @100

SELECT RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName,
SUM(CASE WHEN OrdersHistory.GrossTotal < '100'
THEN OrdersHistory.GrossTotal END) AS '100 & BELOW',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '100' AND OrdersHistory.GrossTotal < '200'
THEN OrdersHistory.GrossTotal END) AS '101-200',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '200' AND OrdersHistory.GrossTotal < '300'
THEN OrdersHistory.GrossTotal END) AS '201-300',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '300' AND OrdersHistory.GrossTotal < '400'
THEN OrdersHistory.GrossTotal END) AS '301-400',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '400' AND OrdersHistory.GrossTotal < '500'
THEN OrdersHistory.GrossTotal END) AS '401-500',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '500' AND OrdersHistory.GrossTotal < '600'
THEN OrdersHistory.GrossTotal END) AS '501-600',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '600' AND OrdersHistory.GrossTotal < '700'
THEN OrdersHistory.GrossTotal END) AS '601-700',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '700' AND OrdersHistory.GrossTotal < '800'
THEN OrdersHistory.GrossTotal END) AS '701-800',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '800' AND OrdersHistory.GrossTotal < '900'
THEN OrdersHistory.GrossTotal END) AS '801-900',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '900' AND OrdersHistory.GrossTotal < '1000'
THEN OrdersHistory.GrossTotal END) AS '901-1000',

SUM(CASE WHEN OrdersHistory.GrossTotal >= '1000'
THEN OrdersHistory.GrossTotal END) AS '1001 & Up',

SUM(@'100 & BELOW'),

SUM(OrdersHistory.GrossTotal) AS 'Total'

FROM OrdersHistory
JOIN RestaurantMaster
ON OrdersHistory.RestaurantID = RestaurantMaster.PKID

WHERE
OrdersHistory.OrderDate >= '2011-10-01' AND OrdersHistory.OrderDate < '2011-11-01'
--AND (DATEPART(HOUR, OrdersHistory.OrderDate) >= 0 AND DATEPART(HOUR, OrdersHistory.OrderDate) < 24)
AND OrdersHistory.StatusFKID = 2

GROUP BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName
ORDER BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName;

--
I want to sum up the result in each case condition.
How can I do that where you can put two aggregate function in one condition?

Is possible to use like,
SUM(101-200)

Can I do that?
Thanks.


AkiEn09
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2011-12-21 : 01:51:54
Assuming that the GrossTotal is of numeric data type. Then there isn't a need to specify the numbers in single quote.


SELECT	RestaurantMaster.RestaurantCode, 
	RestaurantMaster.RestaurantName,
	SUM(CASE WHEN OrdersHistory.GrossTotal <  100 THEN OrdersHistory.GrossTotal END) AS '100 & BELOW',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 100 AND OrdersHistory.GrossTotal < 200 THEN OrdersHistory.GrossTotal END) AS '101-200',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 200 AND OrdersHistory.GrossTotal < 300 THEN OrdersHistory.GrossTotal END) AS '201-300',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 300 AND OrdersHistory.GrossTotal < 400 THEN OrdersHistory.GrossTotal END) AS '301-400',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 400 AND OrdersHistory.GrossTotal < 500 THEN OrdersHistory.GrossTotal END) AS '401-500',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 500 AND OrdersHistory.GrossTotal < 600 THEN OrdersHistory.GrossTotal END) AS '501-600',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 600 AND OrdersHistory.GrossTotal < 700 THEN OrdersHistory.GrossTotal END) AS '601-700',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 700 AND OrdersHistory.GrossTotal < 800 THEN OrdersHistory.GrossTotal END) AS '701-800',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 800 AND OrdersHistory.GrossTotal < 900 THEN OrdersHistory.GrossTotal END) AS '801-900',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 900 AND OrdersHistory.GrossTotal < 1000 THEN OrdersHistory.GrossTotal END) AS '901-1000',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 1000 THEN OrdersHistory.GrossTotal END) AS '1001 & Up',
	SUM(OrdersHistory.GrossTotal) AS 'Total'
FROM 	OrdersHistory
JOIN 	RestaurantMaster
ON 	OrdersHistory.RestaurantID = RestaurantMaster.PKID
WHERE	OrdersHistory.OrderDate >= '2011-10-01' 
AND 	OrdersHistory.OrderDate <  '2011-11-01'
AND 	OrdersHistory.StatusFKID = 2
GROUP BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName

UNION ALL

SELECT	'', 
	'',
	SUM(CASE WHEN OrdersHistory.GrossTotal <  100 THEN OrdersHistory.GrossTotal END) AS '100 & BELOW',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 100 AND OrdersHistory.GrossTotal < 200 THEN OrdersHistory.GrossTotal END) AS '101-200',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 200 AND OrdersHistory.GrossTotal < 300 THEN OrdersHistory.GrossTotal END) AS '201-300',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 300 AND OrdersHistory.GrossTotal < 400 THEN OrdersHistory.GrossTotal END) AS '301-400',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 400 AND OrdersHistory.GrossTotal < 500 THEN OrdersHistory.GrossTotal END) AS '401-500',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 500 AND OrdersHistory.GrossTotal < 600 THEN OrdersHistory.GrossTotal END) AS '501-600',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 600 AND OrdersHistory.GrossTotal < 700 THEN OrdersHistory.GrossTotal END) AS '601-700',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 700 AND OrdersHistory.GrossTotal < 800 THEN OrdersHistory.GrossTotal END) AS '701-800',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 800 AND OrdersHistory.GrossTotal < 900 THEN OrdersHistory.GrossTotal END) AS '801-900',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 900 AND OrdersHistory.GrossTotal < 1000 THEN OrdersHistory.GrossTotal END) AS '901-1000',
	SUM(CASE WHEN OrdersHistory.GrossTotal >= 1000 THEN OrdersHistory.GrossTotal END) AS '1001 & Up',
	SUM(OrdersHistory.GrossTotal) AS 'Total'
FROM 	OrdersHistory
JOIN 	RestaurantMaster
ON 	OrdersHistory.RestaurantID = RestaurantMaster.PKID
WHERE	OrdersHistory.OrderDate >= '2011-10-01' 
AND 	OrdersHistory.OrderDate <  '2011-11-01'
AND 	OrdersHistory.StatusFKID = 2

ORDER BY RestaurantCode, RestaurantName;


And i assumed that you are / will be using MS SQL Server

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akien_ghie09
Starting Member

22 Posts
Posted - 2011-12-21 : 02:09:20
Thanks Khtan!

Yes, I will be using a MS SQL Server.

Thanks again!



AkiEn09
Go to Top of Page

akien_ghie09
Starting Member

22 Posts
Posted - 2011-12-21 : 02:21:45
It has an error.

ERROR: All queries combined using a UNION, INTERSECT or EXCEPT
operator must have an equal number of expressions in their
target lists.
Error Code: 205

Thanks.

AkiEn09
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2011-12-21 : 02:29:05
please post the query that you used

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akien_ghie09
Starting Member

22 Posts
Posted - 2011-12-21 : 02:53:43
quote:
Originally posted by khtan

please post the query that you used

KH
[spoiler]Time is always against us[/spoiler]



SELECT RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName,
SUM(CASE WHEN OrdersHistory.GrossTotal < 100
THEN OrdersHistory.GrossTotal END) AS '100 & BELOW',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 100 AND OrdersHistory.GrossTotal < 200
THEN OrdersHistory.GrossTotal END) AS '101-200',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 200 AND OrdersHistory.GrossTotal < 300
THEN OrdersHistory.GrossTotal END) AS '201-300',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 300 AND OrdersHistory.GrossTotal < 400
THEN OrdersHistory.GrossTotal END) AS '301-400',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 400 AND OrdersHistory.GrossTotal < 500
THEN OrdersHistory.GrossTotal END) AS '401-500',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 500 AND OrdersHistory.GrossTotal < 600
THEN OrdersHistory.GrossTotal END) AS '501-600',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 600 AND OrdersHistory.GrossTotal < 700
THEN OrdersHistory.GrossTotal END) AS '601-700',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 700 AND OrdersHistory.GrossTotal < 800
THEN OrdersHistory.GrossTotal END) AS '701-800',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 800 AND OrdersHistory.GrossTotal < 900
THEN OrdersHistory.GrossTotal END) AS '801-900',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 900 AND OrdersHistory.GrossTotal < 1000
THEN OrdersHistory.GrossTotal END) AS '901-1000',

SUM(CASE WHEN OrdersHistory.GrossTotal >= 1000
THEN OrdersHistory.GrossTotal END) AS '1001 & Up',

SUM(OrdersHistory.GrossTotal) AS 'Total'

FROM OrdersHistory
JOIN RestaurantMaster
ON OrdersHistory.RestaurantID = RestaurantMaster.PKID

WHERE
OrdersHistory.OrderDate BETWEEN '2011-10-01' AND '2011-11-01'
--AND (DATEPART(HOUR, OrdersHistory.OrderDate) >= 0 AND DATEPART(HOUR, OrdersHistory.OrderDate) < 24)
AND OrdersHistory.StatusFKID = 2

GROUP BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName

UNION ALL

SELECT '100 & BELOW', '101-200', '201-300', '301-400', '401-500', '501-600', '601-700', '701-800', '801-900', '901-1000', '101 & UP',
SUM(CASE WHEN OrdersHistory.GrossTotal < 100 THEN OrdersHistory.GrossTotal END) AS '100 & BELOW',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 100 AND OrdersHistory.GrossTotal < 200 THEN OrdersHistory.GrossTotal END) AS '101-200',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 200 AND OrdersHistory.GrossTotal < 300 THEN OrdersHistory.GrossTotal END) AS '201-300',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 300 AND OrdersHistory.GrossTotal < 400 THEN OrdersHistory.GrossTotal END) AS '301-400',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 400 AND OrdersHistory.GrossTotal < 500 THEN OrdersHistory.GrossTotal END) AS '401-500',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 500 AND OrdersHistory.GrossTotal < 600 THEN OrdersHistory.GrossTotal END) AS '501-600',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 600 AND OrdersHistory.GrossTotal < 700 THEN OrdersHistory.GrossTotal END) AS '601-700',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 700 AND OrdersHistory.GrossTotal < 800 THEN OrdersHistory.GrossTotal END) AS '701-800',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 800 AND OrdersHistory.GrossTotal < 900 THEN OrdersHistory.GrossTotal END) AS '801-900',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 900 AND OrdersHistory.GrossTotal < 1000 THEN OrdersHistory.GrossTotal END) AS '901-1000',
SUM(CASE WHEN OrdersHistory.GrossTotal >= 1000 THEN OrdersHistory.GrossTotal END) AS '1001 & UP',
SUM(OrdersHistory.GrossTotal) AS 'Total'

FROM OrdersHistory
JOIN RestaurantMaster
ON OrdersHistory.RestaurantID = RestaurantMaster.PKID

WHERE OrdersHistory.OrderDate >= '2011-10-01' AND OrdersHistory.OrderDate < '2011-11-01'
AND OrdersHistory.StatusFKID = 2

ORDER BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName;

AkiEn09
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2011-12-21 : 03:05:57
the number column of both query in the UNION must be same.

removed those that i strike off

UNION ALL

SELECT '', '',
'100 & BELOW', '101-200', '201-300', '301-400', '401-500', '501-600', '601-700', '701-800', '801-900', '901-1000', '101 & UP',
SUM(CASE WHEN OrdersHistory.GrossTotal < 100 THEN OrdersHistory.GrossTotal END) AS '100 & BELOW',


the 2 empty string in red is to match with column RestaurantCode and RestaurantName of the first query

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akien_ghie09
Starting Member

22 Posts
Posted - 2011-12-21 : 03:11:07
Okay.
Sorry, my mistake.



AkiEn009
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site