I'm trying to create a query that counts the number if devices issued, but pulls in the serial number of just the last item. This code works but the number is always one because the serial is always unique.
select
s.last_name,
s.first_name,
s.student_number,
DECODE (schoolID, '3261', 'CE', '4711', 'PE', '3332', 'RP', '3411', 'WE',
'7143', 'CES', '5077', 'CMS', '3333', 'PWHS', 'Other'),
DECODE (DeviceType, '1', 'Desktop/Laptop', '2', 'Tablet', '3', 'Chromebook', '4', 'Smartphone', '5', 'Other', '6', 'None'),
sd.DeviceSN,
COUNT(DISTINCT sd.DeviceSN)
FROM students s, S_STU_LEARNINGPREF_C sd
Where sd.studentsdcid = s.DCID
GROUP
BY s.last_name,
s.first_name,
s.student_number,
DECODE (schoolID, '3261', 'CE', '4711', 'PE', '3332', 'RP', '3411', 'WE',
'7143', 'CES', '5077', 'CMS', '3333', 'PWHS', 'Other'),
DECODE (DeviceType, '1', 'Desktop/Laptop', '2', 'Tablet', '3', 'Chromebook', '4', 'Smartphone', '5', 'Other', '6', 'None'),
sd.DeviceSN