This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes How do i get result that i want? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How do i get result that i want?" Watch "How do i get result that i want?" New topic
Author

How do i get result that i want?

Supun Lakshan Dissanayake
Ranch Hand

Joined: Oct 26, 2012
Posts: 131

Hi all,
I have a table named 'Sample'



here is the summary

I need to count B1, B2, B3 in the table.
Graph #2 is the output i expected.
Is there any way to get following output by whole sample table or previous Graph #1?
How do i get that from mysql. I tried some group statements but it doesn't work.

here is the sql queries i tried

Thanks!


Are you better than me? Then please show me my mistakes..
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1479
    
  11

So you want to SELECT colB and COUNT all the records (*), then GROUP the results BY colB. It's easier than you think - try looking at some of the examples using SUM(...) instead of COUNT(*) here and here. Use the same pattern for your SQL and you should get it. You don't need DISTINCT here either.


No more Blub for me, thank you, Vicar.
Supun Lakshan Dissanayake
Ranch Hand

Joined: Oct 26, 2012
Posts: 131

chris webster wrote:So you want to SELECT colB and COUNT all the records (*), then GROUP the results BY colB. It's easier than you think - try looking at some of the examples using SUM(...) instead of COUNT(*) here and here. Use the same pattern for your SQL and you should get it. You don't need DISTINCT here either.


thanks!
But still I couldn't find the answer.

I was expecting a query like

but this is NOT working.
I know we should use tableName after FROM keyword.
Is there anyway set of records consider as table in mysql?

Again Thanks for your time!
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1479
    
  11

Supun Lakshan Dissanayake wrote:

but this is NOT working.
I know we should use tableName after FROM keyword.
Is there anyway set of records consider as table in mysql?

Again Thanks for your time!

I don't understand what you're asking here but it's really not so hard:

  • You have a table called "Sample".
  • You want to fetch the "colB" values from this table.
  • You also want to count how many records exist for each "colB" value, so you need to use the SQL COUNT() function.
  • COUNT(*) is a group function in SQL (just like SUM(...)), so you need a corresponding GROUP BY clause after your WHERE clause.
  • You do not need to do anything at all with "colA" because you don't want to see it in your output.
  • You do not need an inline view here either - just select from your "Sample" table as usual.

  • I'm not going to give you the SQL for this - you should learn how to do this stuff for yourself - but just take the example below (from the pages I linked to above) and re-work it to suit your needs

    Supun Lakshan Dissanayake
    Ranch Hand

    Joined: Oct 26, 2012
    Posts: 131

    chris webster wrote:I don't understand what you're asking here but it's really not so hard:

  • You have a table called "Sample".
  • You want to fetch the "colB" values from this table.
  • You also want to count how many records exist for each "colB" value, so you need to use the SQL COUNT() function.
  • COUNT(*) is a group function in SQL (just like SUM(...)), so you need a corresponding GROUP BY clause after your WHERE clause.
  • You do not need to do anything at all with "colA" because you don't want to see it in your output.
  • You do not need an inline view here either - just select from your "Sample" table as usual.

  • I'm not going to give you the SQL for this - you should learn how to do this stuff for yourself - but just take the example below (from the pages I linked to above) and re-work it to suit your needs



    I DON'T need sql query which will give me the answer.
    and i don't wan't to get sum of colB.
    If you read inserted values you will see there is a pattern. mostly colA and colB repeats many times.
    so I need to get colB and count(colB) from FOLLOWING RESULT.

    It is the only way i can imagine to get there are 2 x B1, 1 x B2, 1 x B3.
    All i'm asking
    SELECT columnName, count (columnName) from (SET OF RESULTS INSTEAD FOR DATABASE_TABLE)
    Is there anyway set of records consider as table in mysql?

    again thanks for your time chris webster.
    chris webster
    Bartender

    Joined: Mar 01, 2009
    Posts: 1479
        
      11

    Ah, OK. So you do need an inline view after all!

    There are slight differences between databases in how these are written e.g. Oracle and MySQL have slightly different requirements as MySQL needs a table alias for the inline view.

    There is also a nice syntax for doing this using a "WITH.." clause to declare the inline view at the start of your query, which makes it much easier to see what's going on in the main body of your SQL. This works in Oracle and PostgreSQL, and it should work in MySQL but I can't test it as I don't have MySQL on my machine.

    Both these queries give results like this:
  • B1 - 2
  • B2 - 1
  • B3 - 1

  • Supun Lakshan Dissanayake
    Ranch Hand

    Joined: Oct 26, 2012
    Posts: 131

    chris webster wrote:Ah, OK. So you do need an inline view after all!

    There are slight differences between databases in how these are written e.g. Oracle and MySQL have slightly different requirements as MySQL needs a table alias for the inline view.

    There is also a nice syntax for doing this using a "WITH.." clause to declare the inline view at the start of your query, which makes it much easier to see what's going on in the main body of your SQL. This works in Oracle and PostgreSQL, and it should work in MySQL but I can't test it as I don't have MySQL on my machine.

    Both these queries give results like this:
  • B1 - 2
  • B2 - 1
  • B3 - 1



  • It works.
    I never knew about inline views.
    does inline views also known as persistancy(I don't even know what is persistancy)?

    this one works fine with MySQL.
    but following is not.

    one last question. what is the theory you used in here(give me a name that's enough. I'll google it)?
    THANKS A LOT chris webster!
    chris webster
    Bartender

    Joined: Mar 01, 2009
    Posts: 1479
        
      11

    "Persistence" means storing something on disk (or some other storage medium) so it persists i.e. not just holding values temporarily in RAM. So in a database application, "persistent" stores are typically database tables.

    A database view is like a pre-defined query against one or more underlying tables. The view does not really contain any data, but it can be treated like a table for query purposes. However, you cannot store data directly in a view because it is not a storage mechanism, just a query. Although some databases such as Oracle allow you to store data to the underlying tables via a view using database triggers.

    An inline view is a query that occurs where you would normally expect to see a table name (or pre-defined view) in a SQL query, which is what we did here. The Oracle/PostgreSQL "WITH..." format is especially useful because it allows you to structure your query more clearly, but from what you say it doesn't work on MySQL. Anyway, this is a very powerful technique for creating complex and sophisticated queries, but you need to be careful to build these queries up gradually, testing each SELECT to make sure it does what you want, and also to pay attention to performance issues such as the use of indexes, inefficient joins/comparisons, repeated queries against the same underlying data etc. Always be sure to test your SQL separately from any Java code: it's much easier to debug your SQL if there is no Java around to cause extra bugs.

    SQL is a very powerful "domain specific language for databases", so if you're using SQL, you need to learn how to use it properly, including things like GROUP BY functions, sub-queries, inline views etc, as well as any database-specific features that may affect how you implement particular functions.
    Supun Lakshan Dissanayake
    Ranch Hand

    Joined: Oct 26, 2012
    Posts: 131

    Thanks a lot Chris Webster!
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: How do i get result that i want?
     
    Similar Threads
    Operator precedence and associativity confusion........
    the algrithm in my homework
    logic:iterate in struts
    Combinations Algorithm
    Complicated SQL query help