help1402

It's UWAweek 47

help1402

This forum is provided to promote discussion amongst students enrolled in CITS1402 Relational Database Management Systems.

Please consider offering answers and suggestions to help other students! And if you fix a problem by following a suggestion here, it would be great if other interested students could see a short "Great, fixed it!" followup message.

How do I ask a good question?

Displaying the 4 articles in this topic

Showing 4 of 684 articles.
Currently 135 other people reading this forum.

UWA week 35 (2nd semester, week 6) ↓

worksheet 2 - question 3 - having (all 4)

3:43pm Tue 27th Aug, ANONYMOUS

Hi,

>From the lectures,

"Column names in HAVING clause must also appear in the GROUP BY list or be contained within an aggregate function." The solution uses HAVING count(cust_id) > 2 and city OR phone IS NOT NULL However, column city, phone are not in the GROUP BY list. Could someone please explain why? Thanks.

worksheet 2 - question 3 - having (all 4)

9:56pm Tue 27th Aug, Jichunyang L.

Hi,

The GROUP BY statement in SQL is used to arrange identical data into groups. This statement is often used with aggregate functions (like COUNT, SUM, MAX, MIN, AVG) to group the result-set by one or more columns. Here’s what happens when you use GROUP BY:

Grouping Data: GROUP BY enables you to combine rows that have the same values in specified columns into summary rows, like gathering all the entries from different employees who work in the same department.

Multiple Columns: You can group by more than one column. For instance, if you group by both department and job title, the resulting summary rows represent unique combinations of department and job title.

GROUP BY gathers rows into groups and aggregates their other column values. HAVING then allows you to apply conditions to these groups.

worksheet 2 - question 3 - having (all 4)

3:26pm Wed 28th Aug, Adam W.

Hi, This has also been updated, there is an issue with the way the conditions in the having are evaluated. HAVING count(cust_id) > 2 AND city OR phone IS NOT NULL should be HAVING COUNT(cust_id) > 2 AND (city IS NOT NULL OR phone IS NOT NULL) You need to have the criteria attached to both of the boolean checks (for city and for phone). It could be that sqlite lets you do this, by evaluating the 'city' as true automatically, then it would be 'TRUE OR phone IS NOT NULL' which would automatically mean its always going to be TRUE. Thanks, Adam. ANONYMOUS wrote:

> Hi, > > >From the lectures, > > "Column names in HAVING clause must also appear in the GROUP BY list or be contained > within an aggregate function." > > The solution uses > > HAVING count(cust_id) > 2 and city OR phone IS NOT NULL > > However, column city, phone are not in the GROUP BY list. > > Could someone please explain why? > > Thanks.

worksheet 2 - question 3 - having (all 4)

1:23pm Thu 29th Aug, Eusha K.

Maybe because the city and phone condition arrears in the WHERE clause? (Since city and phone are column names only, not aggregate functions). As far as I understand, if there are column names in the HAVING clause, they must be in the GROUP BY clause, provided they are not contained in aggregate functions. Please feel free to correct me :)

help1402 articles all ANNOUNCEMENTS recent articles login to post a new article help1402 topics from today and yesterday from the past week from the past two weeks ALL topics, most recent first	login chapter0 unitinfo research projects csforum csse-feedback csbreakdown cssubmit csmarks 2nd semester help fora Algebra Advanced Algorithms Cloud Computing High Performance Computing Intelligent Agents Professional Computing Relational Database Management Systems Systems Programming