CODEWITHSUNDEEP
javaandroidsqlsqlite
ghostrider
ghostrider

Reputation: 5259

sql or sqlite query and getting result in a list

I am new to sql and In my app I am using sqlite DB's and I have trouble in creating a query.

I have a DB with column names to be:

Name1  Name2  Score1  Score2 Winner  Aces1  Aces2

Imagine that thse are the stats of a single game, so the same name can be in name 1 or in name 2 in different rows.

I want the following:

a) Return in a list the player names with a descending number of aces.

For example imagine these rows:

Nick George 39  35  Nick  2   4
Joe  Nick   17  39  Nick  1   0
George Jorge  11  39  Jorge  3  1

Should give me in a list the following: //number or aces

George 7
Nick 2
Joe 1
Jorge 1

b)I want the same with number of wins.

c)and the same avg points.

Can you please give me the query?

Upvotes: 2

Views: 140

Answers (2)

Nirav Ranpara
Nirav Ranpara

Reputation: 13785

select name, SUM(aces) from ( select Name1 as Name, Score1 as Score, Aces1 as Aces from yourtable union all select Name2 as Name, Score2 as Score, Aces2 as Aces from yourtable ) v group by Name order by SUM(aces) desc

Upvotes: 0

podiluska
podiluska

Reputation: 51504

Your problem here is a result of storing your data in a denormalised form. If you read up on data normalisation, then you will store your data in a form that is more amenable to producing these queries easily.

select Winner, COUNT(*) from yourtable group by Winner order by COUNT(*) desc

and

select name, SUM(aces)
from
(
select Name1 as Name, Score1 as Score, Aces1 as Aces from yourtable
union all
select Name2 as Name, Score2 as Score, Aces2 as Aces from yourtable
) v
group by Name
order by SUM(aces) desc

Average points is left as an exercise for the reader.

Upvotes: 4

Related Questions