Counting with SQL (1 Viewer)

[radek225]

I built a query "WplatyKlienci" which sum all of payments "Sumawplat" from my clients "klient".

SELECT SUM(tblWycenaZakonczone.cenanetto) AS Sumawplat, tblKlienci.nazwaSkrocona AS klient
FROM tblWycenaZakonczone INNER JOIN (tblZleceniaZakonczone INNER JOIN TblKlienci ON tblZleceniaZakonczone.id_k = tblKlienci.id_k) ON tblWycenaZakonczone.id_zlecenia = tblZleceniaZakonczone.id_zlecenia
WHERE tblWycenaZakonczone.cenanetto is not null
GROUP BY tblKlienci.nazwaskrocona;

Now I need to know 2 things and I have problem with counting in sql:/
1) what percentage of the total amount represents a particular client.
2) I want to create a graph, so I need to do another query, which shows values form no. 1 only for 10 clients with the largest payments + one position named "others" which represents rest of clients

 

[Twincam]

1) select amount, amount*100 / (select sum(amount) from table) from table

2) I'm thinking about this The last time I had to do something like this I used VBA not SQL.

 

[Galaxiom]

Going to need subqueries.

Firstly get the top clients using the TOP predicate in a subquery. Then derive a field based on whether they one of the top clients of "the others".

Then Group By and Count.

 

[Twincam]

I threw this together in a few mins (it works although the layout is a MESS) because I thought I might hijack your thread, the question being:

At what point does SQL become so complex that it would be better to work in VBA. This example isn't too bad because it's small, but I've been dealing with six/seven deep nesting including loads of joins and unions.

My SQL is now a lot stronger than it was, but my predecessors were clearly much stronger in SQL than in VBA.

And that's where I probably answer my own question, because the major issue wasn't the complexity of the SQL, so much as my inexperience. Having said that, it's all a lot simpler now

Anyway.... here it is. Merry Christmas !!

select sum(t3.pct) as pct, newID as ID from (
select table1.id, round(amount * 100 / ( select sum(amount) from table1 ),2) as pct,iif(T2.ID is Null,'Other',T2.ID) AS newID from table1
left join (
select top 5 * from (
SELECT id, round(amount * 100 / ( select sum(amount) from table1 ),2) as pct from table1
) AS T1
order by T1.pct desc
) AS T2
ON table1.ID = T2.ID
) AS T3
group by newid

 

[radek225]

select sum(t3.pct) as pct, newID as ID from (
select table1.id, round(amount * 100 / ( select sum(amount) from table1 ),2) as pct,iif(T2.ID is Null,'Other',T2.ID) AS newID from table1
left join (
select top 5 * from (
SELECT id, round(amount * 100 / ( select sum(amount) from table1 ),2) as pct from table1
) AS T1
order by T1.pct desc
) AS T2
ON table1.ID = T2.ID
) AS T3
group by newid

Could You Show me this sample(query) in file? It will be helpful for me. Now I can't imagine this, because I don't know what is "pct", "table1" etc.

 

[Twincam]

Table1 ID Amount
1 10
2 20
3 30
4 40
5 50
6 60
7 70
8 80

results:

Query1 pct ID
11.11 4
13.89 5
16.67 6
19.44 7
22.22 8
16.67 Other

so 4,5,6,7,8 are the five highest clients with pct being their percentage of the total (360). The other clients (1-3) sum up to 60 which is 16.67% of the total.