Win a copy of Machine Learning for Business: Using Amazon SageMaker and JupyterE this week in the Jython/Python forum
or Object Design Style Guide in the Object-Oriented programming forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
  • Knute Snortum
Sheriffs:
  • Liutauras Vilda
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Joe Ess
  • salvin francis
  • fred rosenberger

sql GROUP BY and HAVING not working

 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have this sql query which is giving me a result which is not complete(half result).
I want to retrieve all students info from the same class which is from the student table, then group them by class.

This are the queries code.

   SELECT first_name, last_name, username FROM students GROUP BY class        
   HAVING class IN(SS3);
           
   SELECT first_name, last_name, class FROM students GROUP BY class
   HAVING class = 'JSS1';
   SELECT
   user_id,
    first_name,
    last_name,
    class,
   COUNT(*) AS total_students
   FROM
    students
   GROUP BY
   class
    HAVING class IN ('JSS1')

The last query also display just only one info, which is the first "jss1" class student name in the table
but this time shows that there are 17 students who are in jss1

How do I manipulate the query to display all the names of the students in the same class

This is the table structure

CREATE TABLE IF NOT EXISTS `students` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`class` varchar(10) NOT NULL,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(50) NOT NULL,
`username` varchar(20) NOT NULL,
PRIMARY KEY (`user_id`)
)
 
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have tried this query using Self Join in SQL Server and this works for me :

 
Greenhorn
Posts: 3
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Gbenga wale wrote:
How do I manipulate the query to display all the names of the students in the same class



If you want to use GROUP BY and HAVING then go like this:
 
Self destruct mode activated. Instructions for deactivation encoded in this tiny ad.
Sauce Labs - World's Largest Continuous Testing Cloud for Websites and Mobile Apps
https://coderanch.com/t/722574/Sauce-Labs-World-Largest-Continuous
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!