Hi StackOverFlow members
reports = A table name.
Database
CREATE TABLE `reports` (
`id` int(11) NOT NULL auto_increment,
`report_day_name` varchar(20) NOT NULL,
`report_day` varchar(20) NOT NULL,
`report_month` varchar(20) NOT NULL,
`report_year` varchar(20) NOT NULL,
`report_result_number` varchar(20) NOT NULL,
`report_result_text` varchar(20) NOT NULL,
`report_since` varchar(20) NOT NULL,
`report_date` varchar(20) NOT NULL,
`catid` int(11) NOT NULL,
`subjectid` int(11) NOT NULL,
`userid` int(11) NOT NULL,
`groupid` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=78 ;
INSERT INTO `reports` VALUES (73, 'day', '14', '1', '1434 h', '5', 'rate', '1234567890', '1434-1-14', 1, 132, 33, 35);
INSERT INTO `reports` VALUES (74, 'day', '12', '2', '1435 h', '4', 'rate', '1234567890', '1434-2-12', 2, 136, 36, 35);
INSERT INTO `reports` VALUES (75, 'day', '14', '1', '1434 h', '2', 'rate', '1354488730', '1434-1-14', 1, 132, 33, 35);
INSERT INTO `reports` VALUES (76, 'day', '12', '2', '1435 h', '4', 'rate', '1354488730', '1434-2-12', 2, 137, 36, 35);
INSERT INTO `reports` VALUES (77, 'day', '12', '2', '1435 h', '1', 'rate', '1354488730', '1434-2-12', 2, 134, 33, 35);
This is the database table:
id report_result_number subjectid userid
73 5 132 33
74 4 136 36
75 2 132 33
76 4 137 36
77 1 134 33
I want to SUM(reports.report_result_number) where (reports.subjectid) is DISTINCT
when i run this code..
SELECT users.user_id, users.user_name, users.user_country, SUM(reports.report_result_number) AS AllTotal, COUNT(DISTINCT reports.subjectid) AS TotalSubjects FROM users INNER JOIN reports ON users.user_id = reports.userid GROUP BY users.user_id ORDER BY AllTotal DESC LIMIT 4
it returns AllTotal
user_id user_name user_country AllTotal TotalSubjects
36 name country 8 (correct) 2
33 name country 8 (not correct) 2
subjectid
is not unique? – Mr. Llamasubjectid
values for each user, and each has a different value forreport_result_number
, then which one should be summed? – Eric PetroeljeSELECT subjectid FROM reports GROUP BY subject_id HAVING COUNT(*) = 1
. Then join to this by subjectid, and do your sum. Not sure if this is getting at what you want, your question is open to multiple interpretations. – DWright