• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Groupby Question

 
Sree Potluri
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot Martin

Problem solved.
 
Sree Potluri
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic