Database query to merge multiple rows

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

Database query to merge multiple rows

Postby bruno47 » Mon Jan 18, 2010 8:14 pm

I would like to know if there's a query that would allow me to group items from a specific column into a single item as a list (IE. item1, item2, item3, ...) based on having a similar date or some similar entry in another column of the table.

I have a database with multiple tables. For this query I am using two of them:
master and stats.

For this query i use master.date, stats.score where master.stats_id = stats._id

what i would like to do is have all stats.score entries listed as a list based on master.date
so for example if:

Master looked like this:
| date | stats_id
|1-1-10 | 1
|1-1-10 | 2
|1-1-10 | 3
|1-2-10 | 4
|1-2-10 | 5

and stats looked like this
| id | score
| 1 | 100
| 2 | 110
| 3 | 120
| 4 | 200
| 5 | 210

i would have the result
|date | score
|1-1-10|100, 110, 120
|1-2-10|200, 210

The result would be used in a listview that would show all dates and all scores entered on those respective dates.
Thank you for you help.
bruno47
Junior Developer
Junior Developer
 
Posts: 11
Joined: Mon Jan 18, 2010 7:57 pm

Top

Postby jwei512 » Wed Jan 27, 2010 12:27 am

Have you tried using a groupBy in your query()?

http://developer.android.com/reference/ ... teDatabase, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String, java.lang.String)

Try looking at that link, specifically at the groupBy clause.

Once you do that, you can make another query with those results (essentially you're doing a JOIN with the other table) that gets you the results of the dates which you grouped

Let me know if this makes sense.
- jwei

http://thinkandroid.wordpress.com
Examples, Code, and Tutorials
jwei512
Senior Developer
Senior Developer
 
Posts: 135
Joined: Mon Jul 27, 2009 9:35 pm
Location: Palo Alto, CA

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 4 guests