MySQL Query for book cost of a class

问题:

I just need a single MySQL line query for the following.

Lets say there are 2 simple tables: Class and Books

Class
ID--Name-----Students
1------KG-----------20
2------Grade(3)---25
3------Grade(5)---30

Books
ID--ClassId--Title-------------Cost
1-----1--------------Drawing------------------5
2-----3--------------History--------------------25
3-----1--------------A-to-Z--------------------10
4-----2--------------Alphabets---------------20
5-----3--------------Maths--------------------15
6-----2--------------English-------------------30

Lets say:
What we only know is -----> ID of the Class
What we have to find is ---> Book Cost of a class. (Books for Each Students In A Class)

Can I just have a Single Line of Query for it?

回答1:

Try this:

In Single Line:

SELECT SUM(Class.Students * Books.Cost) AS BookCost  FROM Books INNER JOIN Class       ON Books.ClassId = Class.ClassId WHERE Books.ClassId = <CLASS-ID-VALUE>  GROUP BY Books.ClassId

With formatting:

SELECT SUM(Class.Students * Books.Cost) AS BookCost
  FROM Books INNER JOIN Class
   ON Books.ClassId = Class.ClassId
WHERE Books.ClassId = <CLASS-ID-VALUE>
GROUP BY Books.ClassId

回答2:

   SELECT c.Name,
          SUM(b.Price * c.Students) cost
     FROM Class c
LEFT JOIN Books b ON b.ClassId = c.ID
    WHERE c.Students >= 31
 GROUP BY c.ID

回答3:

This should do the trick:

SELECT SUM(cost) AS cost FROM Books WHERE ClassId=? GROUP BY ClassId

Where the question makr is either the ID of the class or part of a prepared statement where you feed it the class ID.

You can retrieve the sum with column name "cost".

回答4:

Something like this:

select c.classId, sum(b.cost) as TotalCost,sum(c.students) as TotalStudents from books as b, class as c where b.classId = class.Id and c.Id = YourKnowClassId group by c.Id;

回答5:

select min(c.Name) as Name, sum(b.Price * c.Students) as Cost
from Class c
left join Books b on b.ClassId = c.ID
where c.Students >= 31
group by c.ID

回答6:

Did you mean something like:

SELECT C.Name AS ClassName, SUM(B.Price) * C.Students AS BookCost
  FROM Class AS C INNER JOIN
       Books AS B ON C.ID = B.ClassId
  WHERE C.ID IN (SELECT ID
                   FROM Class
                   WHERE Students >= 31
                   ORDER BY Students ASC
                   LIMIT 1)
  GROUP BY C.ID;