How do i get result that i want?

[Lakshan Dissanayake]

Hi all,
I have a table named 'Sample'

CREATE TABLE Sample (
	colA varchar(10),
	colB varchar(10),
	colC varchar(10),
	CONSTRAINT PRIMARY KEY(colA, colB, colC)
);

INSERT INTO Sample VALUES
('A1','B1','C1'),
('A1','B1','C2'),
('A1','B1','C3'),
('A1','B1','C4'),

('A1','B2','C1'),
('A1','B2','C2'),

('A2','B1','C1'),
('A2','B1','C2'),
('A2','B1','C3'),
('A2','B1','C4'),

('A2','B3','C1'),
('A2','B3','C2'),
('A2','B3','C3');

here is the summary

----------------------------------------------------------------------------------------
Graph #1
select distinct colB, colC from sample;
+-------+-------+
| colA  | colB  |
+-------+-------+
| A1    |  B1   |
| A1    |  B2   |
| A2    |  B1   |
| A3    |  B3   |
+-------+-------+

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.

Graph #2
+-------+-------+
| colB  | count |
+-------+-------+
| B1    |  2    |
| B2    |  1    |
| B3    |  1    |
+-------+-------+
----------------------------------------------------------------------------------------

here is the sql queries i tried

select distinct colB, count (colB) from sample group by (colA, colB);
select distinct colB, count (colA) from sample group by (colA, colB);

Thanks!

[chris webster]

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]

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
sql query #1 SELECT colB, COUNT(colB) FROM(SELECT DISTINCT colA, colB FROM Sample)
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]

Supun Lakshan Dissanayake wrote:
sql query #1 SELECT colB, COUNT(colB) FROM(SELECT DISTINCT colA, colB FROM Sample)
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

SELECT agent_code,   /* what column do you want to see in your output alongside your count? */
SUM (advance_amount)  /* what group function do you need to use here to get a count instead of SUM()? */
FROM orders /* which table do you want to look at? */
GROUP BY agent_code /* How do you want to group your results i.e. what are the columns you want to see in each row alongside the count? */

[Lakshan Dissanayake]

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

SELECT agent_code,   /* what column do you want to see in your output alongside your count? */
SUM (advance_amount)  /* what group function do you need to use here to get a count instead of SUM()? */
FROM orders /* which table do you want to look at? */
GROUP BY agent_code /* How do you want to group your results i.e. what are the columns you want to see in each row alongside the count? */

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.
select distinct colA, colB from sample; +-------+-------+ | colA | colB | +-------+-------+ | A1 | B1 | | A1 | B2 | | A2 | B1 | | A3 | B3 | +-------+-------+
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]

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.

select colb, count(*) from (select distinct cola, colb from sample) as t1 group by colb order by colb

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.

with t1 as (select distinct cola, colb from sample) select colb, count(*) from t1 group by colb order by colb

Both these queries give results like this:

B1 - 2

B2 - 1

B3 - 1

[Lakshan Dissanayake]

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.

select colb, count(*) from (select distinct cola, colb from sample) as t1 group by colb order by colb

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.

with t1 as (select distinct cola, colb from sample) select colb, count(*) from t1 group by colb order by colb

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)?
select colb, count(*) from (select distinct cola, colb from sample) as t1 group by colb order by colb
this one works fine with MySQL.
but following is not.
with t1 as (select distinct cola, colb from sample) select colb, count(*) from t1 group by colb order by colb
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]

"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]

Thanks a lot Chris Webster!