0

I am using a query to calculate total percentage but I want a '%' symbol to appear next to it. Any ideas that could help me

My query so far is

SELECT
student.StudentID,
student.`Name`,
ROUND (Count(attendance.AttendanceID)*100 / 6  ) AS Total,
student.WorkshopID
FROM
    student 
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY
    student.StudentID,
    student.`Name`
  • 1
    Well it depends on where you want to show the data but in my opinion a query only needs to return the data, the formatting should be done in the application. for example if you show this in a SSRS report, add a format property to the textbox. Casting it as a string will break sorting for example unless you also add leading zeros. All that can be a bit messy – Tom V - try topanswers.xyz Apr 8 '15 at 9:08
  • I am using this in PHP. I have nice little function which dynamically generates the table and I am using another plugin called Datatables which adds pagination and sorting to the table. It's a prototype though so at the moment I just want to show that it can be done – Ali Shaikh Apr 8 '15 at 10:00
1

CAST() might not be needed, try with and without it.

SELECT student.StudentID
    ,student.NAME
    ,CONCAT (
        cast(ROUND(Count(attendance.AttendanceID) * 100 / 6) AS CHAR(20))
        ,'%'
        ) AS Total
    ,student.WorkshopID
FROM student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID
    ,student.Name``

The ROUND function returns integer data and CAST is needed to use the result as a string parameter of CONCAT function. CHAR(20) is really arbitrary - you can use any other string data type.

  • The CAST is unnecessary, at least in this situation. CONCAT puts together strings; a numeric value (as from ROUND) will be automatically converted to a string. – Rick James Apr 8 '15 at 12:39
  • This is why i told it might not be needed. I tried a similar query using MySQL Workbench and the displayed result was of type BLOB. I don't have much experience using MySQL so I went for a safer option. – BuahahaXD Apr 9 '15 at 5:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.