How can I make a SQL query which takes an ID, and returns a name, the first entry associate, and the-CodePudding

I'm using SQLite right now I am trying to have a normal query with two subqueries in the SELECT statement. I know it's incorrect though, because it doesn't run at all.

SELECT 
    stuName, 
    (SELECT stuID, lessDate FROM Lessons WHERE stuID = 66 ORDER BY lessDate LIMIT 1) as firstLesson, 
    COUNT(*) as totalNum
FROM Lessons 
WHERE stuID = 66;

I'm sorry if this is not post-worthy but given how specific this search is I wasn't really sure how to search for it.

CodePudding user response：

It's pretty difficult to create something that will work without the table structure or any test data, but try the below:

SELECT 
    stuID,
    stuName,
    MIN(lessDate) firstLesson, 
    COUNT(*) as totalNum
FROM Lessons 
WHERE stuID = 66
GROUP BY stuID,
    stuName;

You can use a GROUP BY statement with multiple aggregation functions to get the info that you need.

Here's a working DBFiddle.