Tags » GROUP BY

Basic T-SQL

In this article I’m going to outline the basic T-SQL commands for those new to the language, and as a reference for others. 596 more words

Stored Procedure

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