Share via

Please Help me, I Found error it is not contained in either an aggregate function or the GROUP BY clause in SQL SERVER

Rizal Valry 196 Reputation points
2021-09-01T02:23:59.09+00:00

I Have two tables for joined in SQL SERVER128066-sys-admin.png

Table sys_admin_group :
id nama seo deskripsi wkt_input wkt_update admin_input admin_update
1 SA sa sa 2015-09-14 00:00:00 2016-01-22 15:23:33.000 dev dev
2 Tester 2 tester-2 Tester 2 2015-09-18 00:00:00 2016-01-05 14:56:01.000 dev dev
3 Tester 3 tester-3 Tester 3 2015-09-18 00:00:00 2016-01-05 14:56:23.000 dev dev
4 E-Retur eretur E-Retur 2016-06-02 14:02:29 2016-06-02 14:02:29.000 iqbal
5 E-Retur Setting eretur_setting E-Retur Setting 2016-06-02 14:04:19 2016-06-02 14:04:19.000 iqbal
6 E-Retur Manage eretur_manage E-Retur Manage 2016-06-02 14:06:10 2016-06-02 14:06:10.000 iqbal
7 E-Retur Execution eretur_execution E-Retur Execution 2016-06-02 19:21:43 2016-06-02 19:21:43.000 iqbal
8 WEB WEB WEB 2016-06-03 16:02:16 2016-08-09 13:32:51.000 iqbal developer

and then Second Table sys_admin:
id kode nama jk uname pass pass_md5 sts email nohp wkt_input wkt_update admin_input admin_update seo_group sts_adm wkt_sts_adm admin_sts_adm
1 A0001 Developer Pria developer bismillah e172dd95f4feb21412a692e73929961e A 0 2015-09-14 00:00:00 2016-03-08 11:27:52.000 dev dev sa 1 NULL
2 A0002 User Pria user bismillah e172dd95f4feb21412a692e73929961e A 0 2015-09-14 00:00:00 2017-02-09 15:47:46.000 dev user tester-2 9 2017-02-09 15:47:46 developer
17 A0003 Testing Wanita fsdf sdfsdf d58e3582afa99040e27b92b13c8f2280 A 2016-03-07 14:54:55 2017-02-09 15:47:49.000 developer 0 9 2017-02-09 15:47:49 developer
21 A0004 Testing Wanita fsdfdfdfdfdf sdfsdf d58e3582afa99040e27b92b13c8f2280 A 2016-03-07 14:55:35 2017-02-09 15:47:52.000 developer 0 9 2017-02-09 15:47:52 developer
23 A0005 Nama LEngkap Wanita cobalagi dfff 513749c0f5dac39a45cf39204b0a084d A 2016-03-07 14:56:53 2017-02-09 15:47:56.000 developer 0 9 2017-02-09 15:47:56 developer
25 A0007 Ajang Suep Pria ajangsuep bismillah e172dd95f4feb21412a692e73929961e A 2016-03-08 10:57:12 2017-02-09 15:48:00.000 developer developer tester-2 9 2017-02-09 15:48:00 developer
26 A0008 Dadang Rusada Pria dadangrusada bismillah e172dd95f4feb21412a692e73929961e A 2016-03-08 10:59:37 2017-02-09 15:48:03.000 developer developer tester-3 9 2017-02-09 15:48:03 developer
27 A0009 testa Wanita test 123456 e10adc3949ba59abbe56e057f20f883e A 2016-04-28 13:16:10 2016-04-28 13:32:03.000 developer developer tester-2 9 2016-04-28 13:32:03 developer
28 A0010 Slamet Pria sl 1 c4ca4238a0b923820dcc509a6f75849b A 2016-05-31 13:32:57 2017-02-09 15:48:06.000 developer sa 9 2017-02-09 15:48:06 developer
29 A0011 Iqbal Pria iqbal 1 c4ca4238a0b923820dcc509a6f75849b A 2016-06-01 10:21:39 2017-02-09 15:48:10.000 developer sa 9 2017-02-09 15:48:10 developer
30 A0012 Fikri Awaludin Pria fikri fikri 5d4864249b21de08642aa6ff4178b346 A 2016-06-02 14:07:33 2017-01-19 16:43:37.000 iqbal developer WEB 1 NULL
34 A0013 Fikri Awaludin Pria eretur eretur123 7d2c7b230e25db43cf9e763b1805fecd A 2016-06-02 14:08:19 2016-06-02 14:08:36.000 iqbal eretur 9 2016-06-02 14:08:36 iqbal
36 A0014 E-Retur Execute Pria ereturex ereturex123 b54911e933e15d8f82effa40138248b2 A 2016-06-02 19:19:59 2017-02-09 15:48:14.000 iqbal iqbal eretur_execution 9 2017-02-09 15:48:14 developer
37 A0015 Frida Anandani Wanita frida frida f60834ce35581c434a68022f2e84f796 A 2016-08-09 13:36:44 2016-08-09 13:36:44.000 developer WEB 1 NULL
38 A0016 Putri Mulyani Wanita uti 123 202cb962ac59075b964b07152d234b70 A 2016-09-21 15:40:18 2016-09-21 15:40:18.000 developer WEB 1 NULL

I use the following query:

SELECT
sys_admin_group.id,
sys_admin_group.nama,
sys_admin_group.seo,
COUNT(sys_admin.seo_group) AS jml,
sys_admin_group.deskripsi
FROM
vm.sys_admin_group
Left Join vm.sys_admin ON sys_admin_group.seo = sys_admin.seo_group
GROUP BY
sys_admin_group.id
ORDER BY
sys_admin_group.id DESC

but I found error message :
Msg 8120, Level 16, State 1, Line 3
Column 'vm.sys_admin_group.nama' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,367 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. MelissaMa-MSFT 24,191 Reputation points
    2021-09-01T02:31:26.117+00:00

    Hi @RizalValry-4876,

    Since you used COUNT in your query, you have to group by all other columns like below: 

    SELECT  
sys_admin_group.id,  
sys_admin_group.nama,  
sys_admin_group.seo,  
COUNT(sys_admin.seo_group) AS jml,  
sys_admin_group.deskripsi  
FROM  
vm.sys_admin_group  
Left Join vm.sys_admin ON sys_admin_group.seo = sys_admin.seo_group  
GROUP BY     sys_admin_group.id,sys_admin_group.nama,sys_admin_group.seo,sys_admin_group.deskripsi  
    ORDER BY  
    sys_admin_group.id DESC

    Or you could update it like below: 

    SELECT distinct   
sys_admin_group.id,  
sys_admin_group.nama,  
sys_admin_group.seo,  
COUNT(sys_admin.seo_group) over (partition by sys_admin_group.id) AS jml,  
sys_admin_group.deskripsi  
FROM  
vm.sys_admin_group  
Left Join vm.sys_admin ON sys_admin_group.seo = sys_admin.seo_group  
ORDER BY  
sys_admin_group.id DESC

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest