473,578 Members | 3,286 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

problem with Group By

hi !
could someone plz help me on this one,its kinda urgent
i hav a result set which looks something like this
country_code sum(amount)

1 100

2 123

3 213
i hav already used 'group by' clause to get the sum of amounts for
each country code
now i need to find the total sum of sum(amount) in the same query.How
do i do it???

i hav tried using an alias for the aggregated column and then trying to
get the sum of that column
but it does not work

plz help me out

regards,
Sangram

Mar 17 '06 #1
5 1531
<sa**********@g mail.com> wrote in message
news:11******** *************@e 56g2000cwe.goog legroups.com...
hi !
could someone plz help me on this one,its kinda urgent
i hav a result set which looks something like this
country_code sum(amount)

1 100

2 123

3 213
i hav already used 'group by' clause to get the sum of amounts for
each country code
now i need to find the total sum of sum(amount) in the same query.How
do i do it???

i hav tried using an alias for the aggregated column and then trying to
get the sum of that column
but it does not work

plz help me out

regards,
Sangram


select workdept, sum(salary) as salary from emp group by workdept
union
select '-TOTAL' as workdept, sum(salary) as salary from emp

Note that the "-" in front of 'TOTAL" ensures that the TOTAL line will sort
after the other departments.
Mar 17 '06 #2
select country_code, sum(amount) as sum from tablename group by
rollup(dept)
select country_code, sum(amount) as sum from tablename group by
cube(dept)

here, rollup and cube is the same.

if you don't want the '-' in the result, try 'coalesce(count ry_code,4)'
or some others.
sa**********@gm ail.com wrote:
hi !
could someone plz help me on this one,its kinda urgent
i hav a result set which looks something like this
country_code sum(amount)

1 100

2 123

3 213
i hav already used 'group by' clause to get the sum of amounts for
each country code
now i need to find the total sum of sum(amount) in the same query.How
do i do it???

i hav tried using an alias for the aggregated column and then trying to
get the sum of that column
but it does not work

plz help me out

regards,
Sangram


Mar 17 '06 #3
I MIGHT HAV NOT STATED MY PROB CLEARLY.
IN MY RESULT SET I JUST WANT THE TOTAL AMOUNT ie. THE SUM OF ALL SUMS.I
DO NOT WANT THE RESULT SET THAT I HAV SHOWN ABOVE.
SO PLZ HELP ME
REGARDS ,
SANGRAM

Mar 17 '06 #4
In article <11************ **********@z34g 2000cwc.googleg roups.com>,
sa**********@gm ail.com says...
I MIGHT HAV NOT STATED MY PROB CLEARLY.
IN MY RESULT SET I JUST WANT THE TOTAL AMOUNT ie. THE SUM OF ALL SUMS.I
DO NOT WANT THE RESULT SET THAT I HAV SHOWN ABOVE.
SO PLZ HELP ME
REGARDS ,
SANGRAM


If you just want to sum all amounts then
something like 'select sum(amount) from table' is enouhg, no need to
grouop by.
Mar 17 '06 #5
1) Drop the GROUP BY and just SUM() them.
2) Start using ISO country codes instead of integers.

Mar 18 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1401
by: | last post by:
I'm trying to scan a (binary) file for a string matching a particular pattern, and am getting unexpected results. I don't know if this is a bug or just my own misunderstanding of regular expressions. The string I'm searching for is a "versioned file name" of the form: "AMS_epXXXx.flt", where 'XXX' is 1 to 3 numerals, the 'x' is lower case...
16
2643
by: cody | last post by:
I have to write an algorithm with must ensure that objects are put in buckets (which are always 4 in size). The objects have two properties: A and B. It is not allowed that in a bucket are objects with the same A or B value. But there can be more than one object with A = null or B = null in the bucket. Sometimes there is only one valid...
4
334
by: ---- | last post by:
Here are my files (problem follows the code): -------------------------------------------------------------------------- // Group.h #ifndef GROUP_H #define GROUP_H class Group { public:
0
928
by: Harry_Crow | last post by:
I have some problem developing the Schema. The below is part of my xml file. <group name="..."> <Skip/> <Item/> <Skip/> <Item/> <Item/> <group name="..."/>
15
2181
by: mrpolitics | last post by:
So I'm working with PureIRCD (http://sourceforge.net/projects/pure-ircd) and everything was fine untill yesterday when the server crashed. So I did a cold restart and staretd the server back up now it's throwing this stream of errors right away. DOes anyone have any idea what they mean. I havn't changed the source at all since it was...
7
5781
by: George Copeland | last post by:
This is a request for assistance analyzing a problem we are experiencing in our VB6 development environment. All our code is developed in VB6, and our persistance layer is SQL Server. We are planning to convert this project to dot net sometime in the future. Our architecture is an exe project with references to four business object dlls. ...
8
11980
by: Chris Noble | last post by:
I need to check whether a particular user is already a member of an Active Directory Security Group. The following code extract works but only if the user distinguished name is exactly the same as that returned from Active Directory. For example using 'cn=' in the userdn string instead of 'CN=' does not work. As far as I am aware Active...
4
1934
by: pokerboy801 | last post by:
OK, I will try to explain this as clearly and as concise as possible. I am using Access, which has three MS Excel Linked tables, to store call center metrics for reps. My Excel workbook has three separate worksheets, all linked to Access. I have a query that performs some minor calculations, not to hard, and will be ued to drive a report....
105
6104
by: sw | last post by:
Hi all, I have a news website tat is developed on joomla 1.5.Login facility is done thru a login component.On each page,ther s a 'Log In' link that redirects them to a login page.On successful login,user details are set in session as well as cookies and then redirected back to d referring page.The 'Log In' section changes to 'Welcome...
0
1752
by: Leira | last post by:
Hi, I have a problem with grouping. My source XML has <record> elements that have a @name and a @group attribute. It looks something like this: <root> <result> <record name="test1" group="group1"><value>1A</value></record> <record name="test2" group="group1"><value>1B</value></record> <record name="test3"...
0
7847
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7778
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8125
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8290
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7874
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6522
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5664
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
1113
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.