Groupby Unions

hi
I have MSSQL query that performs multiple UNION ,but I would like to perform a GROUPBY on the whole result set.
How Can i do this?
plz help...
bonoWhat about creating a view with the union statement?
On the view you can perform the group by on the whole result set.

Sneaky Pie|||use pubs

select U.city, count(U.city)
from
(
select city from authors
union all
select city from publishers
) U
group by u.city|||Hi HanafiH,

your solution is much better than mine, I didn't know that this could work. So I've learned something new.

Thanks for that

Sneaky Pie

group several rows in a SQL query

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

id value
1 1
2 3
3 5
4 7
5 9

The result I expect from the query are 3 rows: [9, 15,21]:
1st row: 1+3+5 = 9
2nd row: 3+5+7 = 15
3rd row: 5+7+9 = 21

Help is appreciated,
Guidoa few sql commands? how about just one? :)select this.value + prev.value + next.value as result
from daTable as this
inner
join daTable as prev
on prev.id =
( select max(id)
from daTable
where id < this.id )
inner
join daTable as next
on next.id =
( select min(id)
from daTable
where id > this.id )|||Thanks for your response, its awesome, but I dont think it fits my needs. I gave an example with just three consecutive rows accumulated, in real life it will be hundreds of rows.
The number of consecutive rows isnt fixed, either, it depends on user input.

Thank you anyway,
Guido|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

i'm done in this thread|||Asking the question you want anwsered? Hmm....a radical concept.

This code will work provided your IDs are consecutively numbered with no gaps:

declare @.RangeLength
set @.RangeLength = 3

select t1.id,
sum(t2.value)
from [YourTable] t1
inner join [YourTable] t2 on t2.id between t1.id-@.RangeLength+1 and t1.id
group by t1.id

If your IDs are not uniformly sequential, then things get more complex, and might best be solved using a temporary table.|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

I did... not as clearly as I should have, but I did:

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

@.blindman:
Yes, my IDs are consecutively numbered, without gaps. Ill try your solution, many thanks.

Guido|||Maybe this does what you want:WITH t(value, nr) AS
(SELECT value, (ROW_NUMBER() OVER (ORDER BY id) - 1)/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nr(Just replace 17 by the number of rows you want to group.)|||Alternatively, if your SQL doesn't have ROW_NUMBER(), and/or if your "id" column just contains all numbers 1, 2, 3 etc. (which means they already contain the ROW_NUMBER()s), the following gives the same result:WITH t(value, nr) AS
(SELECT value, id/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nrOr possibly even, if your SQL allows grouping by expression:SELECT SUM(value)
FROM myTable
GROUP BY id/17or possibly (in case your SQL doesn't perform integer division):SELECT SUM(value)
FROM myTable
GROUP BY CAST(id/17 AS int)|||Thanks Peter,

I will test your script, too.|||Guido

The following seems to work fine and may be easier to understand.
Again the id's need to be sequential but I believe you said they were.

Mike

select t1.val + t2.val + t3.val as vals
from my_table t1,
my_table t2,
my_table t3
where t1.id < t2.id
and t2.id < t3.id
and t3.id < t1.id + 3;|||Easier to understand than a single join? I don't think so.
Plus, you need to read GNiewerth's second post. Your solution is not scalable.|||you need to read GNiewerth's second post. Your solution is not scalable.
You're right - I didn't see that requirement - please ignore my code then!
Mike

Group data by time slot

I would like to prepare a SQL such that I can perform query on a table
and count the number of record per time slot. The time slot is 2
seconds each, but the beginning time of each time slot is not fix, it
is determined by the create_date of the record, and this is the
example:
Raw Data:
seq cat create_date
-- -- --
151 A 2006-01-25 14:14:20.827
152 B 2006-01-25 14:14:20.983
161 A 2006-01-25 14:14:22.390
162 B 2006-01-25 14:14:22.543
171 A 2006-01-25 14:14:23.997
172 B 2006-01-25 14:14:24.153
181 A 2006-01-25 14:14:25.560
182 B 2006-01-25 14:14:25.717
191 A 2006-01-25 14:14:27.123
192 B 2006-01-25 14:14:27.280
Output:
seq cat create_date count
-- -- -- --
151 A 2006-01-25 14:14:20.827 2 -- contain 151, 161
171 A 2006-01-25 14:14:23.997 2 -- contain 171, 181
191 A 2006-01-25 14:14:27.123 1 -- contain 191
152 B 2006-01-25 14:14:20.983 2 -- contain 152, 162
172 B 2006-01-25 14:14:24.153 2 -- contain 172, 182
192 B 2006-01-25 14:14:27.280 1 -- contain 192
Any idea on how the SQL should write?
I just think of a SQL to get the intermediate grouping on which time
slot the record belongs to, but no idea on how to eliminate the
duplicated entry which is already included in another time slot....
The unfinished SQL:
SELECT m.cat, m.create_date AS 'gp_create_date', m.seq, n.seq,
n.create_date, n.random_string
FROM bbs m
LEFT OUTER JOIN bbs n ON n.cat = m.cat
AND n.create_date > m.create_date AND
datediff(ss, m.create_date, n.create_date) < 2
ORDER BY m.create_dateTry this
CREATE TABLE #Temp(seq int, cat CHAR(1), cdate datetime)
INSERT INTO #Temp
SELECT
151, 'A', '2006-01-25 14:14:20.827' UNION ALL
SELECT 152, 'B', '2006-01-25 14:14:20.983' UNION ALL
SELECT 161, 'A', '2006-01-25 14:14:22.390' UNION ALL
SELECT 162, 'B', '2006-01-25 14:14:22.543' UNION ALL
SELECT 171, 'A', '2006-01-25 14:14:23.997' UNION ALL
SELECT 172, 'B', '2006-01-25 14:14:24.153' UNION ALL
SELECT 181, 'A', '2006-01-25 14:14:25.560' UNION ALL
SELECT 182, 'B', '2006-01-25 14:14:25.717' UNION ALL
SELECT 191, 'A', '2006-01-25 14:14:27.123' UNION ALL
SELECT 192, 'B', '2006-01-25 14:14:27.280'
SELECT MIN(seq) as seq, cat, min(cdate) as mindate,max(cdate) as maxdate,
count(*) as cnt
FROM #Temp
GROUP BY cat, YEAR(cdate), Month(cdate), day(cdate),
DATEPART(hh,cdate),DATEPART(mi,cdate),
CASE WHEN DATEPART(s,cdate) %2 = 0
THEN DATEPART(s,cdate)
ELSE DATEPART(s,cdate) - 1
END
DROP tABLE #Temp
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"John Shum" <eurostar@.gmail.com> wrote in message
news:1138177022.986492.77030@.o13g2000cwo.googlegroups.com...
>I would like to prepare a SQL such that I can perform query on a table
> and count the number of record per time slot. The time slot is 2
> seconds each, but the beginning time of each time slot is not fix, it
> is determined by the create_date of the record, and this is the
> example:
> Raw Data:
> seq cat create_date
> -- -- --
> 151 A 2006-01-25 14:14:20.827
> 152 B 2006-01-25 14:14:20.983
> 161 A 2006-01-25 14:14:22.390
> 162 B 2006-01-25 14:14:22.543
> 171 A 2006-01-25 14:14:23.997
> 172 B 2006-01-25 14:14:24.153
> 181 A 2006-01-25 14:14:25.560
> 182 B 2006-01-25 14:14:25.717
> 191 A 2006-01-25 14:14:27.123
> 192 B 2006-01-25 14:14:27.280
> Output:
> seq cat create_date count
> -- -- -- --
> 151 A 2006-01-25 14:14:20.827 2 -- contain 151, 161
> 171 A 2006-01-25 14:14:23.997 2 -- contain 171, 181
> 191 A 2006-01-25 14:14:27.123 1 -- contain 191
> 152 B 2006-01-25 14:14:20.983 2 -- contain 152, 162
> 172 B 2006-01-25 14:14:24.153 2 -- contain 172, 182
> 192 B 2006-01-25 14:14:27.280 1 -- contain 192
> Any idea on how the SQL should write?
> I just think of a SQL to get the intermediate grouping on which time
> slot the record belongs to, but no idea on how to eliminate the
> duplicated entry which is already included in another time slot....
> The unfinished SQL:
> SELECT m.cat, m.create_date AS 'gp_create_date', m.seq, n.seq,
> n.create_date, n.random_string
> FROM bbs m
> LEFT OUTER JOIN bbs n ON n.cat = m.cat
> AND n.create_date > m.create_date AND
> datediff(ss, m.create_date, n.create_date) < 2
> ORDER BY m.create_date
>