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.
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!
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
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