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 OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA 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: 3610
    
  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: 3610
    
  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