wood burning stoves 2.0*
The moose likes JDBC and the fly likes Groupby Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Groupby Question" Watch "Groupby Question" New topic
Author

Groupby Question

Sree Potluri
Ranch Hand

Joined: Aug 12, 2009
Posts: 62
Hi,

I have data in database as

column1 column2 Number of Columns
01A 1 5
01B 2 5
01C 1 3
02A 1 10
02B 3 8
...
15A 1 12
15B 2 4
15C 3 3

I am trying to get the get the count of these devices, so I am using

When I run this query, I am getting
15A 1
01A 1
02A 1
15B 2
01B 2
02B 3

What I need is

01A 1
02A 1
15A 1
01B 2
02B 3
15B 2

and also if I have multiple rows with columns1, columns2, then how do I get the count of them
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I'm not sure whether I understand your requirement, but something like this could do it:Order by clause may contain several expressions and the database will sort by them in the order in which they are specified. One column may appear in several, even all of those expressions.
Sree Potluri
Ranch Hand

Joined: Aug 12, 2009
Posts: 62
Thanks a lot Martin

Problem solved.
Sree Potluri
Ranch Hand

Joined: Aug 12, 2009
Posts: 62
One more question,

if I have another column say column3. I am writing a query



can I embed even this in the query

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Try adding column3 into both select and group by parts of the statement I've shown you.

If this is not what you wanted, you'll have to specify your needs more precisely.
Sree Potluri
Ranch Hand

Joined: Aug 12, 2009
Posts: 62
Hi Martin,

That's not what I wanted.



I want the output
column1 column2 Total
01A 1 5
01B 2 5
01C 1 3
02A 1 10
02B 3 8
Sree Potluri
Ranch Hand

Joined: Aug 12, 2009
Posts: 62
Got it :

select column1, column2, count(distinct column1, column2, column3 ) as number_of_rows from table group by column1, column2 order by substring(column1,3,3), column1, column2


Thanks Martin for helping me with the solution.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Groupby Question
 
Similar Threads
Runtime Creating Tables
Castor -- using GROUP BY to retrieve data from database
Castor -- using GROUP BY to retrieve data from database
Advanced understanding of JTables
Castor -- using GROUP BY to retrieve data from database