Cannot do the SQL query in python via sqlite3 to find out the unique numbers-CodePudding

I have a database. These database has two tables.

One table is music.

name	Date	Edition	Song_ID	Singer_ID
LA	01.05.2009	1	1	1
Second	13.07.2009	1	2	2
Mexico	13.07.2009	1	3	1
Let's go	13.09.2009	1	4	3
Hello	18.09.2009	1	5	(4,5)
Don't give up	12.02.2010	2	6	(5,6)
ZIC ZAC	18.03.2010	2	7	7
Blablabla	14.04.2010	2	8	2
Oh la la	14.05.2011	3	9	4
Food First	14.05.2011	3	10	5
La Vie est..	17.06.2011	3	11	8
Jajajajajaja	13.07.2011	3	12	9

And another table called singer

Singer	nationality	Singer_ID
JT Watson	USA	1
Rafinha	Brazil	2
Juan Casa	Spain	3
Kidi	USA	4
Dede	USA	5
Briana	USA	6
Jay Ado	UK	7
Dani	Australia	8
Mike Rich	USA	9

Now I would like to find out how many Songs are there. I gave that code, but it says invalid sytax??

SELECT DISTINCT Song_ID FROM music

The invalid syntax is near DISTINCT

I create the database like these, maybe that is why it has error:

import sqlite3

conn = sqlite3.connect('musicten.db')  
c = conn.cursor()

c.execute('''
          CREATE TABLE IF NOT EXISTS singer
          ([Singer_ID] INTEGER PRIMARY KEY, [Singer] TEXT, [nationality] TEXT)
          ''')
          
c.execute('''
          CREATE TABLE IF NOT EXISTS music
          ([SONG_ID] INTEGER PRIMARY KEY, [SINGER_ID] INTEGER SECONDARY KEY, [name] TEXT, [Date] DATE, [EDITION] INTEGER)
          ''')
                     
conn.commit()





import sqlite3

conn = sqlite3.connect('musicten.db') 
c = conn.cursor()
                   
c.execute('''
          INSERT INTO singer (Singer_ID, Singer,nationality)

                VALUES
                (1,'JT Watson',' USA'),
                (2,'Rafinha','Brazil'),
                (3,'Juan Casa','Spain'),
                (4,'Kidi','USA'),
                (5,'Dede','USA')
          ''')

c.execute('''
          INSERT INTO music (Song_ID,Singer_ID, name, Date,Edition)

                VALUES
                (1,1,'LA',01/05/2009,1),
                (2,2,'Second',13/07/2009,1),
                (3,1,'Mexico',13/07/2009,1),
                (4,3,'Let"s go',13/09/2009,1),
                (5,(4,5),'Hello',18/09/2009,1)
          ''')

But I don't think, because this works fine. I can not do the sql code

CodePudding user response：

Do you want a count query here:

SELECT COUNT(DISTINCT Song_ID) FROM music;

Assuming that every Song_ID be unique in the music table, you don't even need a distinct count; a regular count will also work:

SELECT COUNT(Song_ID) FROM music;

CodePudding user response：

Try this:

SELECT DISTINCT "Song_ID" FROM music;

CodePudding user response：

I would also try SELECT DISTINCT COUNT(Song_ID) from music;