0

I have db like this:

For meaning: LOP: class
SINHVIEN: student
KHOA: department
MONHOC: subject
DIEMTHI: mark

I want to do the query to list ALL the CLASS and the number of CLASS belong to each DEPARTMENT

SELECT khoa.makhoa,tenkhoa,malop,tenlop FROM khoa,lop 
WHERE khoa.makhoa=lop.makhoa 
ORDER BY khoa.makhoa 
COMPUTE COUNT(malop) BY khoa.makhoa

enter image description here

and the result look like enter image description here

But, the SQL 2012 NO LONGER support COMPUTE, they said it's can be done with ROLL UP but I can't do with its syntax, please help me

Update 1: RADAR's result, with more help enter image description here

Andiana
  • 1,912
  • 5
  • 37
  • 73

1 Answers1

0

you can replace COMPUTE with ROLLUP and GROUP BY

SELECT khoa.makhoa,tenkhoa,malop,tenlop , COUNT(malop) FROM khoa,lop 
WHERE khoa.makhoa=lop.makhoa 
GROUP BY khoa.makhoa,tenkhoa,malop,tenlop
WITH ROLLUP
radar
  • 13,270
  • 2
  • 25
  • 33
  • I run it, and the result have many NULL cell, and count column cover many "1", it doesn't seem like the result which I am looking :) – Andiana Oct 13 '14 at 01:35
  • 1
    If you need to match to a recordset, post the actual recordset, not a report. – Nick.Mc Oct 15 '14 at 12:58