# sql on
Hi people i have problem and can somebody help me:

I have table like this:

Place   Amount
1001       100
1001        150
1002        200
1003         50
1002        200

I have to get tabel like this

Count    Position       Place    Exp.                         Amount
001        751200       1001      B     19.07.11              203,25
002        751200       1002      B     19.07.11              325,20
003        751200       1003      B     19.07.11                  40,5
004        280110                     B      19.07.11               131,05
005        751200        5030     B       19.07.11             -700,00

Explanation:
I need to group place for example 1001 and sum amount and I get 250,
but i have number with wich i have to multiply (0,1869918699186992)
and in count 001 it has to be calculation of this 250-(250*(0,1869918699186992))
and difference i had to sum and put in count 004
and in count 005 it has to be sum of everething but in minus.

I hope it is undestandable, thx a lot.

Comment
Watch Question

Do more with EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Pls. explain followings

What is count?
What is Position?
What is exp?

Commented:
Count is RowCount, 1,2,3,4,5
Position is fixed 751200, for everything except for sum of calculation 280110 (count 004)
Exp. is it has to be written - B and date(today)

Commented:
what is the equation to get value  131.05   and  -700.00?

How do you get value 280110 for Position and  5030 for Place?

Commented:
131, 05 is mispelled it should be 131,9, sorry

1001 >250    (250*(1-100/123)) = 46,75
1002 >400    (400*(1-100/123)) = 74,80
1003 >  50    (50*(1-100/123)) =      9,35
----------------------------------------------------------
131,90

280110 and 5030 are fixed values and should be just written.

Thank you very much.

Commented:
Pls. try following code, you have to add fields for your fixed values into query

``````-- Create Table
CREATE TABLE TableA
(
Place int,
Amount decimal
)

-- Insert Records into Table
INSERT INTO TableA (Place, Amount)
SELECT
1001 AS Place,
100 AS Amount
UNION ALL
SELECT
1001 AS Place,
150 AS Amount
UNION ALL
SELECT
1002 AS Place,
200 AS Amount
UNION ALL
SELECT
1003 AS Place,
50 AS Amount
UNION ALL
SELECT
1002 AS Place,
200 AS Amount

-- Get Desired Result
SELECT
Place,
Sum(Amount) as Column1,
Sum(Amount) * 0.1869918699186992 AS Column2,
Sum(Amount) - (Sum(Amount) * 0.1869918699186992) as Column3
FROM
TableA
GROUP BY
Place

UNION ALL

SELECT
0 AS Place,
0 AS Column1,
Sum(Column2) as Column2,
0 AS Column3
FROM
(
SELECT
Place,
Sum(Amount) as Column1,
Sum(Amount) * 0.1869918699186992 AS Column2,
Sum(Amount) - (Sum(Amount) * 0.1869918699186992) as Column3
FROM
TableA
GROUP BY
Place
) as NewTable
``````

Commented:
Amounts are not fixed, but they are something like this 4454,86
i put like this because it is easier to follow.

Thx

Commented:
In code, pls. ignore line no 1 to 30 because, i have added it to create sample data and table for testing purpose only.

Commented:
This is great thank you, everything is ok, but how do i get only recordcount for row.
1
2
3
4
???

Thank you this was very helpful

Commented:
modified query

``````SELECT
row_number() OVER (ORDER BY Place) AS SrNo,
Place,
Sum(Amount) as Column1,
Sum(Amount) * 0.1869918699186992 AS Column2,
Sum(Amount) - (Sum(Amount) * 0.1869918699186992) as Column3
FROM
TableA
GROUP BY
Place

UNION ALL

SELECT
0 AS SrNo,
0 AS Place,
0 AS Column1,
Sum(Column2) as Column2,
0 AS Column3
FROM
(
SELECT
Place,
Sum(Amount) as Column1,
Sum(Amount) * 0.1869918699186992 AS Column2,
Sum(Amount) - (Sum(Amount) * 0.1869918699186992) as Column3
FROM
TableA
GROUP BY
Place
) as b
``````

Top Expert 2011

Commented:
like this
``````;with cte as
(select place,sum(amount) as amttot
from yourtable
group by place)
,cte1 as
(select place,amttot
,convert(decimal(10,2),round(amttot*0.1869918699186992,2)) as nett
,row_number() over (order by place) as rn
from cte)
,cte2 as
(select sum(amttot)-sum(nett) as nett
,max(rn)+1 as rn
from cte1)
,cte3 as
(select sum(nett) nett

,max(rn)+2 as rn
from cte1)
,cte4 as
(select rn,-(nett+(select nett from cte2)) as full
from cte3
)
select rn as [count]
,'751200' as position
,convert(char(4),place) as place
,'B' as [Exp.]
,convert(char(8),getdate(),4)
,nett as amount
from cte1
union all
select rn,'280110',space(4),'B',convert(char(8),getdate(),4)
,nett
from cte2
union all
select rn,'751200','5030','B',convert(char(8),getdate(),4)
,full
from cte4
order by 1
select rn,'751200','5030','B',convert(char(8),getdate(),4)
,nett
``````

Commented: