Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I group by with Union

Posted on 2010-11-08
19
Medium Priority
?
338 Views
Last Modified: 2012-05-10
Hello EE,

I need to show in a DataGridView (in Vb.NET) a grid like this one:

Number                     Description       Year           Per1       Per2          Per3
  0001                           Test                2010         100$       50$            75$
                                                            2009         45$         10$            80$
                                                            2011          39$        70$           110$

  0002                           Test2              2010        (and so on)  
                                                            2009        .............
                                                            2011        ...............

  0003 ..........


So  to explain more the query, for Each Number i Need 3 rows. The first Row is the year that the user choose inside a combox, then the 2 next rows is the last year of the value chosen and the 3rd is the next year.

the $ values are inside a table.
My problem is that I think I need to do UNION so that it can retreive 3 rows but I dont understand how I can Group By Number (After doing an UNION)    

any ideas ?
0
Comment
Question by:PhilippeRenaud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +2
19 Comments
 
LVL 4

Expert Comment

by:vinodch
ID: 34086850
Use Partition by clause
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 34086858
I dont know that function ..
can you give a little example showing my problem ?

Many thanks if you can..
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 34086867
Why not select the information from a subselect in the FROM clause and within that have your union and then do your group by after that.  Example:

select example1,example2,example3
from (select example1,example2,example3
from t1
union all
example1,example2,example3
from t2 )
group by example1,example2,example3
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 12

Expert Comment

by:Nathan Riley
ID: 34086875
Sorry quick syntax fix.

select example1,example2,example3
from (select example1,example2,example3
from t1
union all
select example1,example2,example3
from t2 ) as tbl
group by example1,example2,example3
0
 
LVL 4

Expert Comment

by:vinodch
ID: 34086879
Select
*
From
(
Select *, -- write your columns instead os *
            Row_Number() over(Partition by TB.Year order by TB.Year desc) as      RowNumber
From       tblUser TB
)
Where RowNumber <= 3
0
 
LVL 4

Expert Comment

by:vinodch
ID: 34086886
Sorry below is the syntax

Select
*
From
(
Select *, -- write your columns instead os *
            Row_Number() over(Partition by TB.Year order by TB.Year desc) as RowNumber
From      tblUser TB
)A
Where RowNumber <= 3
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 34087174
something like this?

select a.Number, a.Description, b.year, b.per1, b.per2, b.per3
from Table1 a
left join (
  select Year, sum(..)  Per1, sum(...) Per2, sum(...) Per3
  from sometabel
  where year in (2009,2010,2011)
  group by number, Year
) b on a.number=b.number

I joined to another table to get description column...
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34087296
I'm pretty sure you mean "group by" as in "group for display". You can do that by using an ORDER BY in the query. eg.
Note the addition of the "orderby" column to make the rows appear in the exact order.

select Number, Description, Year, Per1, Per2, Per3
FROM
(
select 1 as orderby, Number, Description, Year, Per1, Per2, Per3 from tbl where year = 2010
union all
select 2, Number, Description, Year, Per1, Per2, Per3 from tbl where year = 2010-1
union all
select 3, Number, Description, Year, Per1, Per2, Per3 from tbl where year = 2010+1
) SQ
ORDER BY Number, orderby
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 34087379
The 'as orderby' is only at the first select ?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34087393
The names of columns in a unioned query is defined by the first part, the other parts of a union all line up by position, so no need to name them.
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 34087426
no need for union ;)

select Number, Description, Year, Per1, Per2, Per3
from myTable
where year between 2010 -1 and 2010+1
order by abs(year-2010), year

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 34087454
Look, I will do a CREATE Table then put values in it. then try a code from you CyberKiwi.
Because my Results is not like my DataGrid in my example.

give me 10mins then you can try my query with real values of temp tables
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34087514
HainKurt,

I was only showing how to order unioned results.
Obviously the queries inside could be really complex and may be completely different.
If we can see the real query, then it is possible it can be written without using union.
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 34087719
ok start that query. Youll see that its close but not right.

Result are  showing the Num on each row. I would need to show only one time Per 3 rows. I did try to put '' on the other select but does not work.

Also in my tmp2 table there is no 2011. but I would need it even if its not there so the user can input on it on vb.net form (datagridview user can add rows with datasource)
DECLARE @tmp1 table ( [Num] varchar(50)
					, [Desc] varchar(50) )
INSERT INTO @tmp1 VALUES('0001', 'Num Test 1')
INSERT INTO @tmp1 VALUES('0002', 'Num Test 2')

--SELECT * FROM @tmp1


DECLARE @tmp2 table ( [Num] varchar(50)
					, [dteYear] varchar(4)
					, [Per1] money
					, [Per2] money
					, [Per3] money )
INSERT INTO @tmp2 VALUES('0001', '2009', 50, 100, 150)
INSERT INTO @tmp2 VALUES('0001', '2010', 500, 455, 144)
INSERT INTO @tmp2 VALUES('0001', '2008', 111, 19, 90)
INSERT INTO @tmp2 VALUES('0002', '2009', 233, 24, 70)
INSERT INTO @tmp2 VALUES('0002', '2010', 12, 224, 703)


SELECT SQ.Num, SQ.dteYear, SQ.Per1, SQ.Per2, SQ.Per3
FROM
(
select 1 as orderby, a.Num, a.dteYear, a.Per1, a.Per2, a.Per3
from @tmp2 a
INNER JOIN @tmp1 g on g.Num = a.Num
where a.dteYear = 2010
union all
select 2, a.Num, a.dteYear, a.Per1, a.Per2, a.Per3
from @tmp2 a
INNER JOIN @tmp1 g on g.Num = a.Num
where a.dteYear = 2010-1
union all
select 3, a.Num, a.dteYear, a.Per1, a.Per2, a.Per3 
from @tmp2 a
INNER JOIN @tmp1 g on g.Num = a.Num
where a.dteYear = 2010+1
) SQ
ORDER BY SQ.Num, orderby

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34087783
Give this a go
DECLARE @tmp1 table ( [Num] varchar(50)
					, [Desc] varchar(50) )
INSERT INTO @tmp1 VALUES('0001', 'Num Test 1')
INSERT INTO @tmp1 VALUES('0002', 'Num Test 2')

--SELECT * FROM @tmp1


DECLARE @tmp2 table ( [Num] varchar(50)
					, [dteYear] varchar(4)
					, [Per1] money
					, [Per2] money
					, [Per3] money )
INSERT INTO @tmp2 VALUES('0001', '2009', 50, 100, 150)
INSERT INTO @tmp2 VALUES('0001', '2010', 500, 455, 144)
INSERT INTO @tmp2 VALUES('0001', '2008', 111, 19, 90)
INSERT INTO @tmp2 VALUES('0002', '2009', 233, 24, 70)
INSERT INTO @tmp2 VALUES('0002', '2010', 12, 224, 703)

select
	case when t.orderby=1 then N.Num end Num,
	case when t.orderby=1 then t.dteYear end dteYear,
	SQ.Per1, SQ.Per2, SQ.Per3
from
(	select distinct Num from @tmp2) N cross join
(	select 1 as orderby, 2010 as dteYear union all 
	select 2, 2009 union all
	select 3, 2011) t
left join @tmp2 SQ on SQ.Num=N.Num and SQ.dteYear=t.dteYear
order by N.Num, t.orderby

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 34087823
Ok nice, its working almost perfectly, the only thing is huh, I need to show the Year at each row, im having Nulls on 2 rows for each Num

Not sure what to change in your code .. lol sorry :)
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 34087868
Ok I just erased the second Case When and now its fine. = )

Thanks
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34087876
Ah... I looked at the question and saw that the first 2 columns were blank on rows 2/3, so I blanked out the 2nd row in the query... not realising it is different from the question.

select
      case when t.orderby=1 then N.Num end Num,
      case when t.orderby=1 then t.dteYear end dteYear,
      SQ.Per1, SQ.Per2, SQ.Per3
from

to

select
      case when t.orderby=1 then N.Num end Num,
      t.dteYear,  -- change this line
      SQ.Per1, SQ.Per2, SQ.Per3
from
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 37834545
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question