SQLTeam.com | Weblogs | Forums

Subquery Help

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

Welcome

Is this for Microsoft SQL Server?

Looks like Oracle to me.