sql query constructing formula

Question

i have a table with two column:

Name    Values
----------------

Target   20

v1       10

v2       20

v3       10

Total    30

percent   ?

i want to calculate the percentage with the single column value to get the formula as

--> Target/Total * 100.

i.e for ex: Total = SUM (Values) in that way......

by using two rows in the column in sql query, how to do calculation in single column with diff row values by implementing a formula in it.

i dont want to calculate total. i want to calculate percent:

formula:Target/Total * 100 . that gives me the value in percent row?

how? to query it?

Answer 1

If you want to this in SQL...

;WITH
cTarget AS (SELECT Values AS Target FROM MyTable WHERE name = 'Target'),
cTotals AS (SELECT SUM(Values) AS Total FROM MyTable WHERE name <> 'Target')
SELECT * FROM MyTable
UNION ALL
SELECT 'Total', Total FROM cTotals
UNION ALL
SELECT
    'Percentage', 1.0 * Target / Total * 100
FROM
    cTarget
    CROSS JOIN
    cTotals

Answer 2

You want an additional record to show up in your query output? That seems like you're trying to make it look & work like an Excel spreadsheet, but anyways, here's how you'd go about doing it:

SELECT Name, Values FROM table 
UNION (Select function(inputs) AS Name, function(inputs) AS Values FROM table)