Tags » GROUP BY

Common rule to create or validate the GROUP BY clause

Level: Intermediate

Do you know that the content of the GROUP BY clause can be derived from the rest of the clauses in SELECT statement? 386 more words

Tricks And Workarounds

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

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