Hi,
you could try something like this (the outer join is needed so all departments are included in the result and the order-by should list the results the way you want them). select D.[DEPT_NAME], COUNT(*) from [DEPARTMENTS] D left outer join [STUDENTS] S on S.[DEPT_NO] = D.[DEPT_NO] group by D.[DEPT_NAME] order by 2 desc, 1 As to the differences between 4D SQL and other vendors, I just write my query and see what 4D objects to. Cam Adams Programmer [https://s3-ap-southeast-2.amazonaws.com/genie-email-sigs/Gensol_Logo_new.png] P (07) 3870 4085 F (07) 3870 4462 W geniesolutions.com.au This email has been sent from Genie Solutions. The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. When addressed to our customers any opinions or advice contained in this email are subject to the terms and conditions expressed in the Customer Service Level Agreement. [https://s3-ap-southeast-2.amazonaws.com/genie-email-sigs/gensol_Tri.png] ________________________________ From: 4D_Tech <4d_tech-boun...@lists.4d.com> on behalf of Balinder Walia <balinder.wa...@gmail.com> Sent: Wednesday, 21 September 2016 07:47 To: 4D iNug Technical Subject: Standard Sub Select SQL query not working in 4D as expected Guys, I have a SQL sub select query which works in SQLite DB and I have cloned the same DB into 4Dv15.2 DB as the following structure shows: <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE base SYSTEM "http://www.4d.com/dtd/2007/base.dtd" > <base> <table name="Departments" uuid="8CBD8A7D5F61436BAFB704188B209F2A" id="13" prevent_journaling="true"> <field name="DEPT_ID" uuid="2FD49951AF784E37AEB6E1DEDB5A9761" type="4" unique="true" id="1"> <field_extra/> </field> <field name="DEPT_NAME" uuid="04EF7B4537014495AD0EB727F054BDC4" type="10" limiting_length="80" id="2"> <field_extra/> </field> <primary_key field_name="DEPT_ID" field_uuid="2FD49951AF784E37AEB6E1DEDB5A9761"/> <table_extra> <editor_table_info fields_ordering="1" displayable_fields_count="7"> <color red="255" green="255" blue="255" alpha="0"/> <coordinates left="1392.43359375" top="71.58203125" width="170.30078125" height="192.00390625"/> </editor_table_info> </table_extra> </table> </base> <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE base SYSTEM "http://www.4d.com/dtd/2007/base.dtd" > <base> <table name="Students" uuid="8CB0D58F50CD4341864846D8922F32E6" id="14" prevent_journaling="true"> <field name="STUDENT_ID" uuid="E539618DB44648D6BAF6BB73C5929A9E" type="4" unique="true" id="1"> <field_extra/> </field> <field name="STUDENT_NAME" uuid="89FB29C342B54140A309AD4320DC7F13" type="10" limiting_length="80" id="2"> <field_extra/> </field> <field name="GENDER" uuid="DFA84F3E5D6C446094DA9169FB62ACB6" type="10" limiting_length="80" id="3"> <field_extra/> </field> <field name="DEPT_ID" uuid="BDD20CEE2B9A4F11B9BB93477D0EA825" type="4" id="4"> <field_extra/> </field> <primary_key field_name="STUDENT_ID" field_uuid="E539618DB44648D6BAF6BB73C5929A9E"/> <table_extra> <editor_table_info fields_ordering="1" displayable_fields_count="9"> <color red="255" green="255" blue="255" alpha="0"/> <coordinates left="1676.79296875" top="64.98046875" width="195.40625" height="231.62109375"/> </editor_table_info> </table_extra> </table> </base> When run the same query in 4D SQL it doesn't work: *SELECT* Departments*.*DEPT_NAME*, (**SELECT* COUNT*(*) **FROM* Students *WHERE* Departments*.*DEPT_ID=Students*.*DEPT_ID*) **AS* StudentCount *FROM* Departments *ORDER* *BY* StudentCount *DESC**, *Departments*.*DEPT_NAME I get Error when executing the method "_Test7" at line number 12 Generic parsing error. Parsing failed in or around the following substring interval - ( 204, 210 ) - ...E, (SELECT CO... Error code: 1301 Generic parsing error. Parsing failed in or around the following substring interval - ( 204, 210 ) - ...E, (SELECT CO... component: 'SQLS' task -27, name: 'P_4' I believe the SQL syntax for 4D is different than other vendors. What are the differences in SQL in 4D than standard SQLs? This is what I have to accomplish: A university uses 2 data tables, Students and Departments, to store data about its students and the departments associated with each major. Write a query to print the respective department name and number of students majoring in each department for all departments in the Departments table (even ones with no current students). Sort your results by descending number of students; if two or more departments have same number of students, then sort those departments alphabetically by department name. Help!!! ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com ********************************************************************** ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **********************************************************************