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 fin开发者_运维问答d is ---> Book Cost of a class. (Books for Each Students In A Class)

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

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

   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

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

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;

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

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;