How to query three columns and total only one column?

Question

I have a table called Library, I need all columns such as title, author and type. I only want to count all the titles.

I have tried this:

SELECT A.TITLE || ' - ' || A.Author || ' - ' || A.Type AS Title, Count(TITLE) 
From Library A 
group by title

This only gives me Title and Count, I want to see the authors and type as well.

And this:

SELECT TITLE, AUTHOR, TYPE COUNT(TITLE)  
FROM LIBRARY  
GROUP BY TITLE 

I couldn't get a word table to post. So I added as image. This is sample data with output.

Sample data with output

Answer 1

You have to make your mind clear WHAT EXACTLY do you count. For example, "Over the hills and far away" - there are at least three (maybe more) songs with this name (title) but different authors and centuries and lyrics (content).

So you have to DECIDE for youself WHAT that is specifically that you do count, what set of data exactly makes a book identity, an invariant.

• TITLES only regardless of author?
• Or AUTHOR+TITLE tuples?
• Or even some more details maybe? for example the same novel of a same author can be published in different years and even by different publishers, and while those are all the same book for a reader, those are different books for a stock keeper.

And after you made your mind (which is not easy) you have to make sample tables that try to "break the rules". If you made one ideal set of data and one ideal query and got good results, it does NOT mean that "program works". It would only mean so if the results are still good for ANY initial data, including data sets made to compromise the program and to confuse it.

The following queries would all work over your table and would all return "good results", but those might be different results. Or same. Depending on the source data.

SELECT TITLE, TYPE COUNT(TITLE)  
FROM LIBRARY  
GROUP BY TITLE

SELECT TITLE, TYPE COUNT(1)  
FROM LIBRARY  
GROUP BY TITLE

SELECT TITLE, AUTHOR, COUNT(1)  
FROM LIBRARY  
GROUP BY TITLE, AUTHOR

SELECT TITLE, LIST(AUTHOR), COUNT(1)  
FROM LIBRARY  
GROUP BY TITLE

SELECT TITLE, MIN(AUTHOR), MAX(AUTHOR), COUNT(1)  
FROM LIBRARY  
GROUP BY TITLE 

All of those queries are correct, and all of them might return the same data on simplistic source table ("program works"), and still them would return different results for more diverse source data, that tends to happen in real life.

And, there is no way you can choose which of those queries produce the results you wish, before you make the problem clear to yourself and then to us.

Andthen you would have to make data CONSTRAINTs for your tables, like PRIMARY KEYs and UNIQUE INDEXes which make inserting wrong data into the table impossible, and then you would have also to create regular INDEXes that make searching (selecting) faster, but would make changing (insert/update/delete) slower.

Because for a table that has no indexes some trivial queries even with just as few as one thousand books might result in a million or billion of searches and became unbearably slow. If it works with satisfactory for a tiny set 10 books, it does not mean it still would work for a small set of 1000 books, less so for a middle-sized set of 100K-1M books. Been there, done that; learn from my past experience, or you would have to learn from yours...

Some examples:

select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;

VERSION | RDB$CHARACTER_SET_NAME                                                                                                      
:------ | :---------------------------------------------------------------------------------------------------------------------------
3.0.5   | UTF8                                                                                                                        

create table library(
  title varchar(20)
  , author varchar(20)
  , kind varchar(20)
  -- , publisher varchar(20)  -- does htis make difference or not ???
  -- , year integer  -- does this make difference or not ???
  -- , ISBN_code varchar (40) -- does this ???
  
--  primary key(title)  -- is this correct?
--  primary key(author, title) -- or is THIS correct
--  primary key (ISBN_code)  -- or maybe this is correct ???
)

✓

insert into library 
select 'Sun and Moon', 'Johanne Arke', 'manuscript' from RDB$Database 
union all
select 'Sun and Moon', 'Noah', 'clay table' from RDB$Database 
union all
select 'Origin of Species', 'Noah', 'manuscript' from RDB$Database 
union all
select 'My Holy War', 'Johanne Arke', 'newspaper' from RDB$Database 

4 rows affected

select * from library -- check our data WAS entered actually

TITLE             | AUTHOR       | KIND      
:---------------- | :----------- | :---------
Sun and Moon      | Johanne Arke | manuscript
Sun and Moon      | Noah         | clay table
Origin of Species | Noah         | manuscript
My Holy War       | Johanne Arke | newspaper 

-- https://stackoverflow.com/a/68535052/976391  option #1
SELECT A.TITLE, A.AUTHOR, A.KIND, (SELECT COUNT(*) FROM LIBRARY B WHERE B.KIND = A.KIND)
FROM LIBRARY A

TITLE             | AUTHOR       | KIND       | COUNT
:---------------- | :----------- | :--------- | ----:
Sun and Moon      | Johanne Arke | manuscript |     2
Sun and Moon      | Noah         | clay table |     1
Origin of Species | Noah         | manuscript |     2
My Holy War       | Johanne Arke | newspaper  |     1

-- https://stackoverflow.com/a/68535052/976391  option #2
SELECT A.TITLE, A.AUTHOR, A.KIND, B.Total
FROM LIBRARY A 
JOIN (select KIND ,COUNT(*) AS Total from LIBRARY GROUP BY KIND) B 
ON A.KIND = B.KIND

TITLE             | AUTHOR       | KIND       | TOTAL
:---------------- | :----------- | :--------- | ----:
Sun and Moon      | Johanne Arke | manuscript |     2
Sun and Moon      | Noah         | clay table |     1
Origin of Species | Noah         | manuscript |     2
My Holy War       | Johanne Arke | newspaper  |     1

SELECT TITLE, AUTHOR, COUNT(1)  
FROM LIBRARY  
GROUP BY TITLE, AUTHOR

TITLE             | AUTHOR       | COUNT
:---------------- | :----------- | ----:
My Holy War       | Johanne Arke |     1
Origin of Species | Noah         |     1
Sun and Moon      | Johanne Arke |     1
Sun and Moon      | Noah         |     1

SELECT TITLE, MIN(AUTHOR), MAX(AUTHOR), LIST(AUTHOR), COUNT(1)  
FROM LIBRARY  
GROUP BY TITLE

TITLE             | MIN          | MAX          | LIST                      | COUNT
:---------------- | :----------- | :----------- | :------------------------ | ----:
My Holy War       | Johanne Arke | Johanne Arke | Johanne Arke              |     1
Origin of Species | Noah         | Noah         | Noah                      |     1
Sun and Moon      | Johanne Arke | Noah         | Johanne Arke,Noah         |     2

db<>fiddle here

Answer 2

for handy library manager use following query

SELECT A.TITLE, A.AUTHOR,A.TYPE,(SELECT COUNT(*) FROM LIBRARY B WHERE B.TYPE= A.TYPE)
FROM LIBRARY A

or use

SELECT A.TITLE, A.AUTHOR, A.TYPE,B.Total
FROM LIBRARY A 
JOIN (select TYPE,COUNT(*) AS Total from LIBRARY GROUP BY TYPE) B 
ON A.TYPE = B.TYPE