mysql sum total amout

table member have a column staff1, and staff2 that take care for customer how can i can count the amount of people that  each staff take care
CREATE TABLE IF NOT EXISTS `member` (
  `idmember` int(11) NOT NULL AUTO_INCREMENT,
  `own` varchar(255) DEFAULT NULL,
  `Sname` varchar(255) DEFAULT NULL,
  `Ssurname` varchar(255) DEFAULT NULL,
  `Sex` enum('¿','¿') NOT NULL,
  `Hno` varchar(255) DEFAULT NULL,
  `Moo` varchar(255) DEFAULT NULL,
  `tambol` enum('¿¿¿¿¿¿¿¿¿','¿¿¿¿¿¿¿') NOT NULL,
  `dateofbirth` date DEFAULT NULL,
  `migratedate` date DEFAULT NULL,
  `status` enum('5','4','3','2','1') DEFAULT '5',
  `Unit` int(4) DEFAULT NULL,
  `staff1` int(11) DEFAULT NULL,
  `staff2` int(11) DEFAULT NULL,
  PRIMARY KEY (`idmember`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5186 ;




CREATE TABLE IF NOT EXISTS `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `surname` varchar(255) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

Open in new window

teeraAsked:
Who is Participating?
 
mayankagarwalConnect With a Mentor Commented:
select a.id, a.name, a.surname, (select count(b.idmember) from member b where b.staff1 = a.id)  as count_staff1, (select count(c.idmember) from member c where c.staff2 = a.id)  as count_staff2 from staff a
0
 
mayankagarwalCommented:
select a.id, a.name, a.surname, (select count(b.*) from member b where member where b.staff1 = a.id or b.staff2 = a.id) as count_customer from staff a
0
 
teeraAuthor Commented:
Hi mayankagarwal
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from member b where member where b.staff1 = a.id or b.staff2 = a.id) as count' at line 1
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
mayankagarwalCommented:
select a.id, a.name, a.surname, (select count(b.idmember) from member b where b.staff1 = a.id or b.staff2 = a.id) as count_customer from staff a
0
 
Bhavesh ShahLead AnalysistCommented:

SELECT A.id, A.name, A.surname, Count(B.idmember)CustomerCount
FROM Staff A LEFT OUTER JOIN member B
ON B.staff1 = A.id
OR B.staff2 = A.id
0
 
mayankagarwalCommented:
SELECT A.id, A.name, A.surname, Count(B.idmember) as CustomerCount
FROM Staff A LEFT OUTER JOIN member B
ON (B.staff1 = A.id
OR B.staff2 = A.id)
0
 
sihar86Commented:
what is the difference staff1 and staff2?
0
 
mayankagarwalCommented:
according to me there can be two staff member catering to the same customer
0
 
sihar86Commented:
Thanks for explanation mayankagarwal

Alternative way

SELECT * FROM
(
SELECT a.staff1 staff, MAX(b.name) NAME, MAX(b.surname) surname, COUNT(1) total FROM member a, staff b
WHERE a.staff1=b.id
GROUP BY a.staff1
UNION
SELECT a.staff2 staff, MAX(b.name) NAME, MAX(b.surname) surname, COUNT(1) total FROM member a, staff b
WHERE a.staff2=b.id
GROUP BY a.staff2
)

Open in new window

0
 
teeraAuthor Commented:
Hi sihar86
1248 - Every derived table must have its own alias
0
 
mayankagarwalCommented:
the query i have given is not working??
0
 
teeraAuthor Commented:
hi experts

Every member have staff to take care  member a was take care by staff1 and incase staff1 can not take care staff2 will take care the
   i want to see how many member that staff take care if it to much so will consider to look fore anoter staff to take cate

                     staff1   staff2
david               20       5
robert              19       6
Susan              18       4
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

try this

- Bhavesh
SELECT A.idmember,A.Sname,
		Count(CASE WHEN A.staff1 = B.id THEN 1 ELSE 0 END) AS Staff1Count,
		Count(CASE WHEN A.staff2 = B.id THEN 1 ELSE 0 END) AS Staff2Count
FROM member A LEFT OUTER JOIN staff B
ON A.staff1 = B.id 
OR A.staff2 = B.id
Group By 
	A.idmember,A.Sname

Open in new window

0
 
sihar86Commented:
I forget the table name
for the result staff and amount of member
SELECT * FROM
(
SELECT a.staff1 staff, MAX(b.name) NAME, MAX(b.surname) surname, COUNT(1) total FROM member a, staff b
WHERE a.staff1=b.id
GROUP BY a.staff1
UNION
SELECT a.staff2 staff, MAX(b.name) NAME, MAX(b.surname) surname, COUNT(1) total FROM member a, staff b
WHERE a.staff2=b.id
GROUP BY a.staff2
)

Open in new window


for the result member and amount of staff1 and staff2
SELECT a.name, 
(SELECT COUNT(1) FROM tes b WHERE b.name=a.name AND staff1 <> '') staff1,
(SELECT COUNT(1) FROM tes c WHERE c.name=a.name AND staff2 <> '') staff2
FROM tes a
GROUP BY a.name

Open in new window

0
 
sihar86Connect With a Mentor Commented:
Sorry, I correct my answer before
I forget the table name
for the result staff and amount of member
SELECT * FROM
(
SELECT a.staff1 staff, MAX(b.name) NAME, MAX(b.surname) surname, COUNT(1) total FROM member a, staff b
WHERE a.staff1=b.id
GROUP BY a.staff1
UNION
SELECT a.staff2 staff, MAX(b.name) NAME, MAX(b.surname) surname, COUNT(1) total FROM member a, staff b
WHERE a.staff2=b.id
GROUP BY a.staff2
) x 

Open in new window

for the result member and amount of staff1 and staff2
SELECT a.name, 
(SELECT COUNT(1) FROM tes b WHERE b.name=a.name AND staff1 <> '') staff1,
(SELECT COUNT(1) FROM tes c WHERE c.name=a.name AND staff2 <> '') staff2
FROM tes a
GROUP BY a.name

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.