Grouped information from two tables

Hi,

I have two statements which when I join by a union statement give the folowing:

2005 11 0.000000
2005 12 0.000000
2006 1 0.000000
2006 1 50813.058500
2006 10 0.000000
2006 11 0.000000
2006 12 0.000000
2006 12 63224.511250
2006 2 0.000000
2006 2 59164.234500
2006 3 0.000000
2006 4 0.000000
2006 5 0.000000
2006 6 0.000000
2006 6 82442.570750
2006 7 0.000000
2006 7 61809.497750
2006 8 0.000000
2006 9 0.000000
2007 1 0.000000
2007 2 0.000000
2007 3 0.000000
2007 4 0.000000
2007 5 0.000000
2007 6 0.000000
2007 7 0.000000
2007 8 0.000000

What I want is to merge the values

2006 1 0.000000
2006 1 50813.058500

into one row, the months with zero figures are required.

Thanks

Not the best of descriptions, but I have now solved this problem.

Answer = Ist table joins to second table (Select statement) via a right outer join.

sql

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

GridView based on SQLServerDataSource using a Select Union statement, impacts on Update an

I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):

SELECT
FirstName,
LastName
FROM
Master
UNION ALL
SELECT
FirstName,
LastName
FROM
Custom
ORDER BY
LastName,
FirstName

I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated!

Thanks,

Randy

SELECT
FirstName,
LastName,0 AS Editable
FROM
Master
UNION ALL
SELECT
FirstName,
LastName,1 AS Editable
FROM
Custom
ORDER BY
LastName,
FirstName

Only allow rows that editable is 1 to be edited, then use an update statement directly on custom for the rows that get editted.

|||

Thank you for the direction. I am unclear on your last sentence...can you provide a code snippet that illustrates what you are explaning?

I appreciate the help

Randy

|||

UPDATE Custom SETFirstName=@.FirstName,LastName=@.LastName WHEREFirstname=@.original_FirstName ANDLastName=@.original_LastName

|||

Thanks, the Update statement makes sense.

Last question: when you say "Only allow rows that editable is 1 to be edited", can you provide direction on what the code would be such that the EditTemplate never appears for Editable = 0? (i.e. so that Rows where Editable = 1 can go into edit mode but rows where Editable = 0 cannot).

Sorry for what may be basic questions...

|||

I really can't without knowing more about what it is you are trying to do, or how you've implemented your edit functionality.