Tags » GROUP BY

Interview Question: Can a Select statement have HAVING clause and no GROUP BY?

Question: Can a Select statement have HAVING clause and no GROUP BY?
Level: Intermediate/Advanced.

I was first asked this question about 20 years ago and I have to admit I did not provide the right answer which seemed to be very counter intuitive. 108 more words

Tricks And Workarounds

Grouping by month

You would think grouping by month is a fairly simple task when it comes to SharePoint list items but I have come to find out it wasn’t as easy as I thought.   484 more words

General

Having an issue with "Group By" field selection erased in reports

While creating a summary report, the report does not work for triOrganizationAllocation BO. But the report works fine for a Space BO. In the Group By feature in the report, when any field is selected, post save, then whatever is selected for Group By gets erased. 6 more words

Platform

SQL GROUP BY and Functional Dependencies: A Very Useful Feature

Relational databases define the term “Functional Dependency” as such (from Wikipedia):

In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database.

458 more words
Sql

Java 8 streams collectors groupBy

package com.bawi.java8.groupBy;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

public class MyJavaGroupBy {
    static class Person {
        private final String name;
        private final int age;
        
        public Person(String name, int age) {
            this.name = name;
            this.age = age;
        }
        
        public String getName() {
            return name;
        }
        
        public int getAge() {
            return age;
        }
        
        @Override
        public String toString() {
            return "Person ";
        }
    }
    public static void main(String[] args) {
        List<Person> persons = Arrays.asList(
                new Person("John", 20), 
                new Person("Rob", 20), 
                new Person("Bartek", 22), 
                new Person("Ania", 21));
        
        // Group by person age, default collect to map: key=age, value = list of people (aggregate function is returning as list of persons)
        Map<Integer, List<Person>> peopleListByAge = persons.stream().collect(Collectors.groupingBy(Person::getAge));
        System.out.println(peopleListByAge);
        // {20=, Person ], 21=], 22=]}

        // Group by person age (key), map person to person name and collect the names to set (value), (aggregate function is returning as set of names)  
        Map<Integer, Set<String>> peopleNameSetByAge = persons.stream().collect(Collectors.groupingBy(Person::getAge, Collectors.mapping(Person::getName, Collectors.toSet())));
        System.out.println(peopleNameSetByAge);
        // {20=, 21=, 22=}

        
        // Group by person age (key) and count of person for a given age (value), apply counting aggregate function  
        Map<Integer, Long> peopleByAgeWithCount = persons.stream().collect(Collectors.groupingBy(Person::getAge, Collectors.counting()));
        System.out.println(peopleByAgeWithCount);
        // {20=2, 21=1, 22=1}

    }
} 27 more words
Java

Nested Select Statements in MySQL!

Nested select statements can return a single value, a single row, a column, or a table. The subquery in the first example shown below selects only Clayton Kershaw (playerID = “kershcl01”) in 2014. 314 more words

Order By

SQL语句的解析过程

SQL语句的解析顺序

简单的说一个sql语句是按照如下的顺序解析的:

  • 1. FROM FROM后面的表标识了这条语句要查询的数据源。和一些子句如,(1-J1)笛卡尔积,(1-J2)ON过滤,(1-J3)添加外部列,所要应用的对象。FROM过程之后会生成一个虚拟表VT1。
  • (1-J1)笛卡尔积 这个步骤会计算两个相关联表的笛卡尔积(CROSS JOIN) ,生成虚拟表VT1-J1。
  • (1-J2)ON过滤 这个步骤基于虚拟表VT1-J1这一个虚拟表进行过滤,过滤出所有满足ON 谓词条件的列,生成虚拟表VT1-J2。
  • (1-J3)添加外部行  如果使用了外连接,保留表中的不符合ON条件的列也会被加入到VT1-J2中,作为外部行,生成虚拟表VT1-J3。
  • 2. WHERE 对VT1过程中生成的临时表进行过滤,满足where子句的列被插入到VT2表中。
  • 3. GROUP BY 这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。
  • 4. HAVING 这个子句对VT3表中的不同的组进行过滤,满足HAVING条件的子句被加入到VT4表中。
  • 593 more words
Database