MrTV
asked on
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 ;
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
ASKER
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
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
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
SELECT A.id, A.name, A.surname, Count(B.idmember)CustomerC
FROM Staff A LEFT OUTER JOIN member B
ON B.staff1 = A.id
OR B.staff2 = A.id
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)
FROM Staff A LEFT OUTER JOIN member B
ON (B.staff1 = A.id
OR B.staff2 = A.id)
what is the difference staff1 and staff2?
according to me there can be two staff member catering to the same customer
Thanks for explanation mayankagarwal
Alternative way
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
)
ASKER
Hi sihar86
1248 - Every derived table must have its own alias
1248 - Every derived table must have its own alias
the query i have given is not working??
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
try this
- Bhavesh
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
I forget the table name
for the result staff and amount of member
for the result member and amount of staff1 and staff2
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
)
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.