GROUP BY in UNION separates grouped rows

Hi,

SQL Anywhere 9.0.3.3221

We have a query of 5 smaller queries, bunched up with UNION ALLs, and each 
single query has a GROUP BY statement. For example,

SELECT
client.name,
project.name,
sum(project.hours)
from... where...
GROUP BY
client.name,
project.name
UNION ALL
SELECT
client.name,
project.name,
sum(project.hours)
from... where... /with some different conditions/
GROUP BY
client.name,
project.name
....

We have discovered that GROUPED BY results are not grouped by from different 
select sections - if both the first and second select query return a row 
with the same values, they are still shown as two separate lines:

1. "DAVID"---"OPTIMIZATION"---15 (hours)
2. "ADAM"---"OPTIMIZATION"---10 (hours)
3. "ADAM"---"OPTIMIZATION"---7 (hours)
4. "MIKE"---"OPTIMIZATION"---20 (hours)

Second and third line - we sort of expected them to be one same line of 17 
hours, but they come from different queries, so they are shown separately. I 
guess we discovered that when different query results are UNIONed, then 
GROUP BY is not necessarilly grouping them. Is there a way to sum up those 
two lines?


Cheers


0
Vlad
1/14/2011 2:32:29 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

6 Replies
1051 Views

Similar Articles

[PageSpeed] 38

Vlad,

you are using UNION ALL, and that by definition does not eliminate 
duplicatess from the different select parts.

A simple UNION (or UNION DISTINCT) should return the desired result.

HTH
Volker



Vlad wrote:
> Hi,
>
> SQL Anywhere 9.0.3.3221
>
> We have a query of 5 smaller queries, bunched up with UNION ALLs, and each
> single query has a GROUP BY statement. For example,
>
> SELECT
> client.name,
> project.name,
> sum(project.hours)
> from... where...
> GROUP BY
> client.name,
> project.name
> UNION ALL
> SELECT
> client.name,
> project.name,
> sum(project.hours)
> from... where... /with some different conditions/
> GROUP BY
> client.name,
> project.name
> ...
>
> We have discovered that GROUPED BY results are not grouped by from different
> select sections - if both the first and second select query return a row
> with the same values, they are still shown as two separate lines:
>
> 1. "DAVID"---"OPTIMIZATION"---15 (hours)
> 2. "ADAM"---"OPTIMIZATION"---10 (hours)
> 3. "ADAM"---"OPTIMIZATION"---7 (hours)
> 4. "MIKE"---"OPTIMIZATION"---20 (hours)
>
> Second and third line - we sort of expected them to be one same line of 17
> hours, but they come from different queries, so they are shown separately. I
> guess we discovered that when different query results are UNIONed, then
> GROUP BY is not necessarilly grouping them. Is there a way to sum up those
> two lines?
>
>
> Cheers
>
>
0
Volker
1/14/2011 2:46:25 PM
In article <4d3061c1$1@forums-1-dub>, No_VBarth@Spam_GLOBAL-FINANZ.de 
says...
> 
> Vlad,
> 
> you are using UNION ALL, and that by definition does not eliminate 
> duplicatess from the different select parts.
> 
> A simple UNION (or UNION DISTINCT) should return the desired result.

I don't think that will combine the two Adam's into 17 hours, will it?  
I think it will just return either Adam's 10 or 7 hour rows.

You're going to need a group by over the entire resultset, I think.

> 
> HTH
> Volker
> 
> 
> 
> Vlad wrote:
> > Hi,
> >
> > SQL Anywhere 9.0.3.3221
> >
> > We have a query of 5 smaller queries, bunched up with UNION ALLs, and each
> > single query has a GROUP BY statement. For example,
> >
> > SELECT
> > client.name,
> > project.name,
> > sum(project.hours)
> > from... where...
> > GROUP BY
> > client.name,
> > project.name
> > UNION ALL
> > SELECT
> > client.name,
> > project.name,
> > sum(project.hours)
> > from... where... /with some different conditions/
> > GROUP BY
> > client.name,
> > project.name
> > ...
> >
> > We have discovered that GROUPED BY results are not grouped by from different
> > select sections - if both the first and second select query return a row
> > with the same values, they are still shown as two separate lines:
> >
> > 1. "DAVID"---"OPTIMIZATION"---15 (hours)
> > 2. "ADAM"---"OPTIMIZATION"---10 (hours)
> > 3. "ADAM"---"OPTIMIZATION"---7 (hours)
> > 4. "MIKE"---"OPTIMIZATION"---20 (hours)
> >
> > Second and third line - we sort of expected them to be one same line of 17
> > hours, but they come from different queries, so they are shown separately. I
> > guess we discovered that when different query results are UNIONed, then
> > GROUP BY is not necessarilly grouping them. Is there a way to sum up those
> > two lines?
> >
> >
> > Cheers
> >
> >


0
David
1/14/2011 3:34:44 PM
On Fri, 14 Jan 2011 15:32:29 +0100, Vlad <vlad@nospampcr.ltd.uk> wrote:


> We have discovered that GROUPED BY results are not grouped by from  
> different
> select sections - if both the first and second select query return a row
> with the same values, they are still shown as two separate lines:

it is good that you discovered that. but you also could simply deduce this
 from the documentation of select - union. if you first calculate grouped
rows, then "unionize" them, you get this result. what you need, of course,
is to first "unionize", then group by. and this is exactly the solution.

select guy, activity, sum(hoursoffun) as totalhoursoffun
from
   (select guy, activity, hoursoffun
      from table1
    union all
    select guy, activity, hoursoffun
      from table2
    ...
   ) alltogether
group by guy, activity
0
iso
1/14/2011 3:57:10 PM
I think so too. While surely there are more elegant solutions, this 
should be working :

SELECT clientname,projectname,sum(projecthours) from
(SELECT client.name as clientname,
         project.name as projectname,
         project.hours as projecthours
FROM... WHERE...
UNION ALL
SELECT client.name,
        project.name,
        project.hours
FROM... WHERE... /with some different conditions/
....) DerivedTable
GROUP BY clientname, projectname

HTH
Reimer

  David Kerber wrote:
> In article<4d3061c1$1@forums-1-dub>, No_VBarth@Spam_GLOBAL-FINANZ.de
> says...
>>
>> Vlad,
>>
>> you are using UNION ALL, and that by definition does not eliminate
>> duplicatess from the different select parts.
>>
>> A simple UNION (or UNION DISTINCT) should return the desired result.
>
> I don't think that will combine the two Adam's into 17 hours, will it?
> I think it will just return either Adam's 10 or 7 hour rows.
>
> You're going to need a group by over the entire resultset, I think.
>
>>
>> HTH
>> Volker
>>
>>
>>
>> Vlad wrote:
>>> Hi,
>>>
>>> SQL Anywhere 9.0.3.3221
>>>
>>> We have a query of 5 smaller queries, bunched up with UNION ALLs, and each
>>> single query has a GROUP BY statement. For example,
>>>
>>> SELECT
>>> client.name,
>>> project.name,
>>> sum(project.hours)
>>> from... where...
>>> GROUP BY
>>> client.name,
>>> project.name
>>> UNION ALL
>>> SELECT
>>> client.name,
>>> project.name,
>>> sum(project.hours)
>>> from... where... /with some different conditions/
>>> GROUP BY
>>> client.name,
>>> project.name
>>> ...
>>>
>>> We have discovered that GROUPED BY results are not grouped by from different
>>> select sections - if both the first and second select query return a row
>>> with the same values, they are still shown as two separate lines:
>>>
>>> 1. "DAVID"---"OPTIMIZATION"---15 (hours)
>>> 2. "ADAM"---"OPTIMIZATION"---10 (hours)
>>> 3. "ADAM"---"OPTIMIZATION"---7 (hours)
>>> 4. "MIKE"---"OPTIMIZATION"---20 (hours)
>>>
>>> Second and third line - we sort of expected them to be one same line of 17
>>> hours, but they come from different queries, so they are shown separately. I
>>> guess we discovered that when different query results are UNIONed, then
>>> GROUP BY is not necessarilly grouping them. Is there a way to sum up those
>>> two lines?
>>>
>>>
>>> Cheers
>>>
>>>
>
>

0
R
1/14/2011 3:58:03 PM
Hi Volker,

We tried UNION instead of ALL first thing - it did not make any difference, 
so we came back to UNION ALL.

David, how exactly can I get a group by with the scope of the whole 
resultset? One idea I had was putting everything into one big view and then 
selecting from it, but that is impossible - 4 out of 5 WHERE clauses of 
queries use retrieval arguments in slightly different ways, so putting the 
whole thing into the view is not possible.


Cheers



"David Kerber" <ns_dkerber@ns_warrenrogersassociates.com> wrote in message 
news:MPG.279a371df72aea959896ed@forums.sybase.com...
> In article <4d3061c1$1@forums-1-dub>, No_VBarth@Spam_GLOBAL-FINANZ.de
> says...
>>
>> Vlad,
>>
>> you are using UNION ALL, and that by definition does not eliminate
>> duplicatess from the different select parts.
>>
>> A simple UNION (or UNION DISTINCT) should return the desired result.
>
> I don't think that will combine the two Adam's into 17 hours, will it?
> I think it will just return either Adam's 10 or 7 hour rows.
>
> You're going to need a group by over the entire resultset, I think.
>
>>
>> HTH
>> Volker
>>
>>
>>
>> Vlad wrote:
>> > Hi,
>> >
>> > SQL Anywhere 9.0.3.3221
>> >
>> > We have a query of 5 smaller queries, bunched up with UNION ALLs, and 
>> > each
>> > single query has a GROUP BY statement. For example,
>> >
>> > SELECT
>> > client.name,
>> > project.name,
>> > sum(project.hours)
>> > from... where...
>> > GROUP BY
>> > client.name,
>> > project.name
>> > UNION ALL
>> > SELECT
>> > client.name,
>> > project.name,
>> > sum(project.hours)
>> > from... where... /with some different conditions/
>> > GROUP BY
>> > client.name,
>> > project.name
>> > ...
>> >
>> > We have discovered that GROUPED BY results are not grouped by from 
>> > different
>> > select sections - if both the first and second select query return a 
>> > row
>> > with the same values, they are still shown as two separate lines:
>> >
>> > 1. "DAVID"---"OPTIMIZATION"---15 (hours)
>> > 2. "ADAM"---"OPTIMIZATION"---10 (hours)
>> > 3. "ADAM"---"OPTIMIZATION"---7 (hours)
>> > 4. "MIKE"---"OPTIMIZATION"---20 (hours)
>> >
>> > Second and third line - we sort of expected them to be one same line of 
>> > 17
>> > hours, but they come from different queries, so they are shown 
>> > separately. I
>> > guess we discovered that when different query results are UNIONed, then
>> > GROUP BY is not necessarilly grouping them. Is there a way to sum up 
>> > those
>> > two lines?
>> >
>> >
>> > Cheers
>> >
>> >
>
> 


0
Vlad
1/14/2011 3:58:07 PM
Hello Reimer,

Actually, that worked a treat. Fantastic, thanks!


"R. Pods" <r.pods@gmx.net> wrote in message news:4d30728b@forums-1-dub...
>I think so too. While surely there are more elegant solutions, this should 
>be working :
>
> SELECT clientname,projectname,sum(projecthours) from
> (SELECT client.name as clientname,
>         project.name as projectname,
>         project.hours as projecthours
> FROM... WHERE...
> UNION ALL
> SELECT client.name,
>        project.name,
>        project.hours
> FROM... WHERE... /with some different conditions/
> ...) DerivedTable
> GROUP BY clientname, projectname
>
> HTH
> Reimer
>
>  David Kerber wrote:
>> In article<4d3061c1$1@forums-1-dub>, No_VBarth@Spam_GLOBAL-FINANZ.de
>> says...
>>>
>>> Vlad,
>>>
>>> you are using UNION ALL, and that by definition does not eliminate
>>> duplicatess from the different select parts.
>>>
>>> A simple UNION (or UNION DISTINCT) should return the desired result.
>>
>> I don't think that will combine the two Adam's into 17 hours, will it?
>> I think it will just return either Adam's 10 or 7 hour rows.
>>
>> You're going to need a group by over the entire resultset, I think.
>>
>>>
>>> HTH
>>> Volker
>>>
>>>
>>>
>>> Vlad wrote:
>>>> Hi,
>>>>
>>>> SQL Anywhere 9.0.3.3221
>>>>
>>>> We have a query of 5 smaller queries, bunched up with UNION ALLs, and 
>>>> each
>>>> single query has a GROUP BY statement. For example,
>>>>
>>>> SELECT
>>>> client.name,
>>>> project.name,
>>>> sum(project.hours)
>>>> from... where...
>>>> GROUP BY
>>>> client.name,
>>>> project.name
>>>> UNION ALL
>>>> SELECT
>>>> client.name,
>>>> project.name,
>>>> sum(project.hours)
>>>> from... where... /with some different conditions/
>>>> GROUP BY
>>>> client.name,
>>>> project.name
>>>> ...
>>>>
>>>> We have discovered that GROUPED BY results are not grouped by from 
>>>> different
>>>> select sections - if both the first and second select query return a 
>>>> row
>>>> with the same values, they are still shown as two separate lines:
>>>>
>>>> 1. "DAVID"---"OPTIMIZATION"---15 (hours)
>>>> 2. "ADAM"---"OPTIMIZATION"---10 (hours)
>>>> 3. "ADAM"---"OPTIMIZATION"---7 (hours)
>>>> 4. "MIKE"---"OPTIMIZATION"---20 (hours)
>>>>
>>>> Second and third line - we sort of expected them to be one same line of 
>>>> 17
>>>> hours, but they come from different queries, so they are shown 
>>>> separately. I
>>>> guess we discovered that when different query results are UNIONed, then
>>>> GROUP BY is not necessarilly grouping them. Is there a way to sum up 
>>>> those
>>>> two lines?
>>>>
>>>>
>>>> Cheers
>>>>
>>>>
>>
>>
> 


0
Vlad
1/14/2011 4:08:20 PM
Reply:

Similar Artilces:

Groups of groups
I need to add groups of users to a new group. Is there a way or workaround to do this? A customer is telling me that you can do in AD... Thanks Miguel Cando, Yes AD can do nested groups but eDirectory can't. One way to get around it is to use Dynamic Groups or create a new container and place the objects in there instead if possible. In all my years I've never had the need for nested groups. -- ___________________________________________ Niclas Ekstedt, CNA/CNE/CNS/CLS Network Consultant/NSC Sysop InfraSystems Solutions There is no "group of groups&quo...

Row Evaluation
Hi all I have an interesting question and don't know if an expression can do it. I have a datawindow that has 1 group. Any duplicates within the group are evaluated within an expression and hidden. Then everything is slid up making it look good, however I am getting valid repeats in each group that I would like to hide as well. This is what I see now: Pick Up Company #1 Deliver Company #5 Pick Up Company #1 Deliver Company #8 And this is what I want to see: Pick Up Company #1 Deliver Company #5 Deliver Company #8 Because of the sort criteria I know that if there is ...

group by if groups overlap
What's the way to go if you want to use group by, but the groups overlap. For example: Get the orders if the order date between 2009-01-01 till 2009-01-31 2009-01-01 till 2009-03-31 2009-01-01 till 2009-12-31 Thanks Eric On Thu, 10 Jun 2010 09:40:01 +0200, Ontsnapt <ontsnapt@hotmail.com> wrote: > What's the way to go if you want to use group by, but the groups > overlap. For example: > > Get the orders if the order date between > 2009-01-01 till 2009-01-31 > 2009-01-01 till 2009-03-31 > 2009-01-01 till 2009-12-31 you create a t...

Grouping group details
I have a datawindow created by a join of customer info and transaction info that (simplified) looks like this: Customer name: Service Service Cost Currently, everything is grouped on the name so I would get: Bob Smith Svc1 $10 Svc1 $10 Svc1 $10 Svc2 $20 Svc3 $30 Svc3 $30 Total $110 What I'd like to do instead is get: Bob Smith Svc1 $30 Svc2 $20 Svc3 ...

Groups which a group is a member of
I'm trying to script an output which would tell me the groups which a group is a member of. Does anyone know where I would look for this one? If you have the distinguishedName of the group, you can search the ad for groups that have the attribute member set to this value.So your filter would be:(&(objectClass=group)(member=<dn_of_group>))Here's an article on CodeProject that explains searching with DirectoryServices:Querying Active Directory using .NET classes and LDAP queries If this post was useful to you, please mark it as answer. Thank you!...

join group...which group?
Looking at: https://developer.mozilla.org/en-US/docs/Mozilla/Tech/XPCOM the newsgroup, mailing list, google group, these are all supposed to mirror each other? I'm on the mozilla firefox newsgroup, but mozilla.dev.tech.xpcom is mostly dead, at least recently. Is this google groups mailing list available on gmane, perhaps? gmane.comp.mozilla.devel.xpcom looks like the right group, but it's dead, too. It says that it's a gateway to mozilla-xpcom@mozilla.org, but that's probably broken, I'm guessing. I haven't looked at that mailing list archives, how...

group member of group
Is it possible to assign a group membership in another group like with Microsoft? This would be useful when assigning file systems rights. I have tried making ou's members of groups but the ou does not inherit the file system rights. No - not the way you want to use it. A group can't be a member of another group. You must assign the rights to multiply groups or at OU level. Actualy you can make a group a member of a dynmic group - but file rights assigned to dynamic group is not supported by the file system because it depend on the 'memberOf' attribute on the ...

Make group be member of another group(nestet groups)
Hi! We have an IDM-sollution with wlan-authentication. We run eDir on OES2 Linux that also run iManager 2.7.3 All wlan-traffic goes trough a HP Access Controll Server. The ACS place users in the right VLAN based on group-membership. We are a school and all students in one class is member of its class-group. What we want is to put this class-group into another group called "exam". So if all students in this class now also are member of "exam", then the HP ACS will put then in its own VLAN that have limited access to Internet. In this case we talk about group-...

Groups within Groups
Hi, Does anyone know if there are plans to enable groups to be members of other groups? It would be so handy! The facility's been available in the Lotus Domino directory for as long as I can remember, and I think it's even possible in <shudder> active directory. Thanks, Iain On Wed, 23 Mar 2005 09:52:17 +0000, Iain McLaren wrote: Yes both Domino and AD has this feature, NDS/eDir doesn't. AFAIK, the issue is the security equivalences nested groups means that you have transitive rights, which NDS/eDir doesn't support. However IIRC, you can do s...

groups within groups
Does the latest Tbird allow groups to be contained within other groups? Bill On 23.05.2014 11:11, William S Krol wrote: --- Original Message --- > Does the latest Tbird allow groups to be contained > within other groups? > Bill Do you have an example? Could it be a feature of the particular groups sofware? -- Jay Garcia - www.ufaq.org - Netscape - Firefox - SeaMonkey - Thunderbird Mozilla Contribute Coordinator Team - www.mozilla.org/contribute/ Mozilla Mozillian Member - www.mozillians.org Mozilla Contributor Member - www.mozilla.org/credits/ On 23.05.2014 ...

Assigning number to a group (group count instead of row count)
I have a datawindow that prints orders with detail lines. The datawindow is set up as: Group 1 : ordnbr Detail: detail lines I need to find a function that will assign a sequential number to each order as it displays it on the datawindow. I tried currentrow(), but this changes with every detail line on an order. Example: Ordnbr : 10000 SeqNbr: 1 detail 1: SeqNbr: 2 detail 2: SeqNbr: 3 Ordnbr: 10001 SeqNbr: 4 I need it to be like this: Ordnbr : 10000 SeqNbr: ...

Using Bugzilla Groups to Limit User Group to Group Bugs
I am maintaining my company's Bugzilla installation, and I am currently investigating whether or not it is possible with Bugzilla's current Group Permissions capabilities to achieve a certain isolation goal. Let's say we have three Products A, B, and C and two Clients 1 and 2. We would like to make it so that all staff can see, edit, and modify all bugs for Products A, B, and C while Client 1 can only see, edit, and modify bugs that someone from Client 1 has entered for Product A and Client 2 can only see, edit, and modify bugs that someone from Client 2 has entered for ...

rows counting in separated groups of report ?
Is there any other way to count rows on report (data window-freeform) except putting Getrow() on Detail band of the data window . When we have made a group collumn , getrow() returned first row number for every group of rows. Getrow() expression was on group header band it can not be on datail band because then group doesn't make a sense. We have tried with computed expressions but it doesn't work. Thank You very much ! CumulativeSum (1 FOR GROUP 1) "Damir Klaric" <damir.klaric@astrum.hr> wrote in message news:IstEfw2mCHA.198@forums.sybase.com... ...

Can DW grouping keep all the rows belongs to the seond group in one page?
I have a dw with 2 groupings. the first group has a page break but the not second. Sometimes rows belongs to the second group will be display across two pages. Can I keep all the rows belongs to the second group on one page, and I don't want to use nested report .... ...

Web resources about - GROUP BY in UNION separates grouped rows - sybase.sqlanywhere.general

Separate Baptists - Wikipedia, the free encyclopedia
... but its sources remain unclear because it lacks inline citations . Please improve this article by introducing more precise citations. The Separate ...

REPORT: Facebook at Work Website Closer to Launch; Personal Timelines to Be Kept Separate
... Times reported Sunday that the new site will contain much of the same functionality as Facebook, but it will keep personal Timelines separate ...

Facebook tests ‘Pages Feed’ so fans can see all updates in separate stream
Some Facebook users are seeing a separate “ Pages Feed ” where they can view all the updates from pages they Like in a single stream. This seems ...

Startup Founders and Entrepreneurs : What separates the top 10% of startup CEOs from the rest?
Answer (1 of 55): I've interviewed thousands of CEOs and some things that stand out to me: 1. Good at hiring AND firing. Whenever you find a ...

J is for Jettison, how Orion safely separates its elements during space flight. - Flickr - Photo Sharing ...
Once Orion reaches orbit, its Launch Abort System is jettisoned or discarded, releasing the spacecraft into space. Prior to re-entry, the service ...

Journey-Separate Ways performed by Recess - YouTube
This video was featured on Yahoo.com's front page April 18, 2009. It was the kids first gig together, video has received over 735,000 views on ...

Two men fight for life after separate stoushes
Police are speaking with a man who has volunteered himself to Petrie officers after a violent night on Brisbane's streets.

Orica to separate chemical arm
Group eyes potential $1bn demerger as it narrows focus on mining services business.

Two die in separate collisions
Two men lost their lives in separate car crashes on the Sunshine Coast and near Goondiwindi at the weekend.

Russell Crowe and Danielle Spencer separate
OSCAR-winning actor Russell Crowe and his wife Danielle Spencer have separated after nine years of marriage.

Resources last updated: 11/20/2015 11:58:19 PM