Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove Grand Total Row......


Remove Grand Total Row......

Author
Message
Grass
Grass
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 342
Hi all,

I am trying to see if any one can help me on how to remove a grand total row from a table. I have a table in which there are 6 columns. first 3 columns there is some data but at first 3 columns, last row is blank and the last 3 columns have data which is added at the last 3 row as a grand total. here it is looks like:

col1 col2 col3 col4 col5 col6
a b c 1 2 5
d e f 4 3 8
g h i 6 7 5
11 12 18


So, the last three column row is grand total and the first 3 columns last row is blank. So, I am trying to delete that but can't.Need help

Thanks.
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
I don't understand your explanations. Post some example data and try to explain again using these data.
Grass
Grass
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 342
I mean my question is that a table have some columns and at the row there is a grand total of a data in the above column. So, if i have 4 columns and the last two columns each row have some numbers which will add at the last row as a total number. The other columns have data also but the last row first two columns doesnt have anything in it just blanks. how to remove the last row.

thanks.


col1 col2 col3 col4 col5 col6
a b c 1 2 5
d e f 4 3 8
g h i 6 7 5
11 12 18
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
You say the rows with the total contain blanks in the first two columns, the other rows do not. So if this is your criteria the query is quite easy:
select * 
from dbo.TableX
where col1 = '' and col2 = ''



Try this select to check if it returns the rows you want to delete. If so replace the "select *" by "delete" and these rows will be deleted.
delete 
from dbo.TableX
where col1 = '' and col2 = ''


Grass
Grass
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 342
OK. You its right if I use this statement to delete this it will delete it. The whole purpose is that I want the last row to be deleted where the grand total of numbers and blank spaces on the other two columns. So if there is another row with blank spaces shows in the two columns.Is it going to delete those rows with this statement?If it does then I don't think this statement will work.
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
I'm still not sure, but yes, the statement above deletes ALL rows that contain the free spaces in the two columns.
If you only want delete the last row there are several possibilities. For the beginning I wold take an easy way. Using the top clause you can select the primary key value of the "last row" in your table. Then you can delete the row using the primary key value.

Let's say there is a column InsertDate in your table und you want to delete only the last inserted row with blanks. Let's also say there is a primary key column Named "Id" in your table.
This would be:

declare @IdToDelete int;
select @IdToDelete = (
select top (1) Id
from dbo.TableX
where col1 = '' and col2 = ''
order by InsertDate desc
)

delete from dbo.TableX where Id = @IdToDelete



Again use a select instead of a delete statement to check the rows the query returns.

For a more exactly reply you really should post the table structure you use and some sample data.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search