add a row to select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

add a row to select query

Author

Topic

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-01-08 : 14:20:18

Hi,

This is a basic SQL query i think but i really don't know how i should code it.

Select top 3 Name, Total from TABLE

result:
Hein, 3
Piet, 4
Rob, 5

But Now I want to add a 4e row to it with a custom text. (I really want to sum the remaining totals, but I already know how to do that, so only the add row problem will do ;))

So the new result:
Hein, 3
Piet, 4
Rob, 5
remaining, 83

thanks

Bjorn

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-01-08 : 14:32:34

After your SELECT, add UNION SELECT 'Remaining',SUM(Total)...

Sarah Berger MCSD

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-08 : 14:40:22

????????????????

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22760

The answers there didn't help you?

- Jeff

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-01-08 : 14:50:24

Yes, It did helped me, but it didn't gave me 1 recordset but 2 or only the sum. So i thought i'll create a new topic because this is another question..

And thanks Sara, i'll give it a try...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-08 : 17:23:18

Did you try my solution?

Select b.id, isnull(b.name, 'Remaining'), sum(a.total)
FROM
Table a
LEFT OUTER JOIN
(SELECT TOP 25 * from Table ORDER BY Total) B
ON
a.id = b.id
GROUP BY b.id, b.name

add

ORDER BY isnull(b.id,9999) to sort the Remaining total to the end of your recordset (make sure 9999 is greater than your highest ID).

If there is no ID field, just use the Name field or its equivalent, and then order by isnull(b.name,'ZZZZZZ').

- Jeff

Edited by - jsmith8858 on 01/08/2003 18:01:58

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-01-09 : 11:42:56

Thanks Jeff,

I combined some of the answers and came up with something working :). My sp is a little bit more complicated so you will have to believe me on my word that it works.... :)

Bjorn

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-01-11 : 06:54:35

Hi (again)

I had some probs with my solution (preformance) so I tryed Jeff's solution. It work, except on 1 thing. If I add the order by isnull(b.id, 99999) ( or isnull(b.name, 99999))the the totals aren't ordered right anymore.

hein, 5
piet, 4
rob, 6
remaining, 12

or something like that. Any idea's?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-11 : 08:07:06

Try:

ORDER BY ISNULL(B.Total, 9999999)

- Jeff

Edited by - jsmith8858 on 01/11/2003 08:36:39

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-01-11 : 08:49:03

mmmm nope:

Column name 'B.total' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-12 : 18:42:53

sorry about that --- just add "b.total" to the GROUP BY and you should be good to go.

I apologize for being a little sloppy on this one!

Select b.id, isnull(b.name, 'Remaining'), sum(a.total)
FROM
Table a
LEFT OUTER JOIN
(SELECT TOP 25 * from Table ORDER BY Total) B
ON
a.id = b.id
GROUP BY b.id, b.name, b.total
ORDER BY ISNULL(B.Total, 9999999)

- Jeff

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-01-13 : 09:39:28

Yes, it works. Thanks. Only one thing, I needed to change the 9999999999 to 0 because it was placed at the top instead of the bottom.

Thanks again.

Bjorn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources