asked on How do I group by with Union
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 ?
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 ?
Microsoft SQL Server 2005Microsoft SQL Server 2008Microsoft SQL Server
Last Comment
Philippe Renaud
Use Partition by clause
ASKER
I dont know that function ..
can you give a little example showing my problem ?
Many thanks if you can..
can you give a little example showing my problem ?
Many thanks if you can..
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
select example1,example2,example3
from (select example1,example2,example3
from t1
union all
example1,example2,example3
from t2 )
group by example1,example2,example3
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
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
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
*
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
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
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
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...
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...
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
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
ASKER
The 'as orderby' is only at the first select ?
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.
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
select Number, Description, Year, Per1, Per2, Per3
from myTable
where year between 2010 -1 and 2010+1
order by abs(year-2010), year
ASKER
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
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
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.
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.
ASKER
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)
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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 :)
Not sure what to change in your code .. lol sorry :)
ASKER
Ok I just erased the second Case When and now its fine. = )
Thanks
Thanks
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
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
ASKER
Hi cyberkiwi, related question for you :
https://www.experts-exchange.com/questions/27671525/SQL-Select-Help.html
https://www.experts-exchange.com/questions/27671525/SQL-Select-Help.html