I’m trying to extract some data from an SQL database and work with it to get some basic figures for a financial report.
Suppose I have a table like the following:
| ID | code | name | total | profit |
|---|---|---|---|---|
| 01 | aa01 | Alan | 209.01 | 54.46 |
| 02 | aa01 | Alan | 121.19 | 21.02 |
| 03 | ab75 | Abby | 67.66 | 5.44 |
| 04 | ch03 | Chris | 998.67 | 100.32 |
| 05 | ch03 | Chris | 107.43 | 23.33 |
I want to be able to select these columns as I have (see below query) and then group by code with the SUM of total and profit. From there I need to get the percentage profit to total which will give me my margin per customer. The code part is unique to each customer so in theory using the below I’d have 3 rows for Alan (2 lines aggregated), Abby & Chris (2 lines also). This would give me an overall spend and overall margin. So far I have the following:
select code, name, total, profit from jobs
group by code
order by total desc
But I can’t get the group by clause to work. I’ve tried adding the other columns to the group by clause but it makes no difference. From there I need to figure out how to “add” a column to my output which is % so essentially profit/total*100.
If anyone can help that would be marvellous.
3
As best I can tell from the text description, you are just missing your aggregate functions e.g.
select code, name, sum(total) total, sum(profit) profit
, convert(decimal(9,4), sum(profit) / sum(total) * 100) PercentageProfit
from jobs
group by code, name;
Returns
| code | name | total | profit | PercentageProfit |
|---|---|---|---|---|
| ab75 | Abby | 67.66 | 5.44 | 8.0402 |
| aa01 | Alan | 330.20 | 75.48 | 22.8588 |
| ch03 | Chris | 1106.10 | 123.65 | 11.1789 |
Fiddle