显示MYSQL中的所有数据

Question

I have a query that should display all the data that matches the where clause but unluckily it only displays 1 data. Here is my query.

SELECT
  CONCAT(member.firstName, ' ', member.middleName, ' ', member.lastName) AS name,
  (SUM(serviceapplication.memShareCap) * (60 / 100))                     AS total_memshare,
  COUNT(serviceapplication.servAppID)                                    AS total_mem,
  serviceapplication.memShareCap                                         AS memsharecap
FROM serviceapplication
  INNER JOIN member ON member.memID = serviceapplication.applyingMember
WHERE serviceapplication.status = 'Active'
      AND member.memType = 'Client'
      AND serviceapplication.CoopInfo_coopID = '1'

How can I display all the data?

Answer 1

Since you are using aggregate functions i.e. sum and count, you are missing a group by clause:

select concat (
        member.firstName,
        ' ',
        member.middleName,
        ' ',
        member.lastName
        ) as name,
    (SUM(serviceapplication.memShareCap) * (60 / 100)) as total_memshare,
    COUNT(serviceapplication.servAppID) as total_mem,
    serviceapplication.memShareCap as memsharecap
from serviceapplication
inner join member on member.memID = serviceapplication.applyingMember
where serviceapplication.status = 'Active'
    and member.memType = 'Client'
    and serviceapplication.CoopInfo_coopID = '1'
group by concat (
        member.firstName,
        ' ',
        member.middleName,
        ' ',
        member.lastName
        ),
    serviceapplication.memShareCap

If you want to get totals for all of the members in each row then use subqueries:

select concat (
        member.firstName,
        ' ',
        member.middleName,
        ' ',
        member.lastName
        ) as name,
    (
        select SUM(serviceapplication.memShareCap) * (60 / 100)
        from serviceapplication
        inner join member on member.memID = serviceapplication.applyingMember
        where serviceapplication.status = 'Active'
            and member.memType = 'Client'
            and serviceapplication.CoopInfo_coopID = '1'
        ) as total_memshare,
    (
        select COUNT(serviceapplication.servAppID)
        from serviceapplication
        inner join member on member.memID = serviceapplication.applyingMember
        where serviceapplication.status = 'Active'
            and member.memType = 'Client'
            and serviceapplication.CoopInfo_coopID = '1'
        ) as total_mem,
    serviceapplication.memShareCap as memsharecap
from serviceapplication
inner join member on member.memID = serviceapplication.applyingMember
where serviceapplication.status = 'Active'
    and member.memType = 'Client'
    and serviceapplication.CoopInfo_coopID = '1';