• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

How do i get result that i want?

 
Ranch Hand
Posts: 143
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Lakshan Dissanayake
Ranch Hand
Posts: 143
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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

     
    Lakshan Dissanayake
    Ranch Hand
    Posts: 143
    Android Java Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    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
    Posts: 2407
    36
    Scala Python Oracle Postgres Database Linux
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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

  •  
    Lakshan Dissanayake
    Ranch Hand
    Posts: 143
    Android Java Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    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
    Posts: 2407
    36
    Scala Python Oracle Postgres Database Linux
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    "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.
     
    Lakshan Dissanayake
    Ranch Hand
    Posts: 143
    Android Java Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thanks a lot Chris Webster!
     
    reply
      Bookmark Topic Watch Topic
    • New Topic