Webbo_1980
asked on
Stuck on an implementation of sum and groupby
Let say i have the following table i.e. .
ID, CLIENTID, TOTAL
1 1 1
2 1 1
3 2 5
4 3 4
5 3 5
I need the to create a sum of TOTAL to output something like
ClientId Total
1 2
2 5
3 9
Can you please confirm how i do this
thanks
Webbo
ID, CLIENTID, TOTAL
1 1 1
2 1 1
3 2 5
4 3 4
5 3 5
I need the to create a sum of TOTAL to output something like
ClientId Total
1 2
2 5
3 9
Can you please confirm how i do this
thanks
Webbo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i created a sample table with the information you posted and it works fine, what data types are your columns?
ASKER
Both are integer and my exact query is...
SELECT Id, sum(Total) as totalSum from mytable
group by Id, Total
order by Id
SELECT Id, sum(Total) as totalSum from mytable
group by Id, Total
order by Id
you dont want to group by total, only ClientID. also, if you select ID it will not work as each record in ID is unique
ASKER
But the problem is this wont give me a total count i.e. if you look at my samples data i.e. client id 1 and 3 you'll see the total values get added not the amount of clientids
Hope this makes sense?
Hope this makes sense?
You may need to re-word your question. You stated that you're looking for a combination of Client ID and their Totals.
The query provided by CaptainGiblets does exactly that and his followup is correct.
You need to change the Id in your query to ClientId
SELECT ClientId, sum(Total) as totalSum from mytable
group by ClientId
order by ClientId
The query provided by CaptainGiblets does exactly that and his followup is correct.
You need to change the Id in your query to ClientId
SELECT ClientId, sum(Total) as totalSum from mytable
group by ClientId
order by ClientId
ASKER
Any suggestions?
770 258
770 10
770 7
770 1709
770 20
770 75
770 18
770 9
793 8
793 14
793 1967