📖
Notes
  • 🙌Welcome!
  • CyberSecurity
    • Penetration Testing
      • ELearnSecurity
        • eJPT
          • Footprinting & Scanning
            • Port Scanning
            • Mapping Networks
          • Information Gathering
            • Subdomain Enumeration
          • Network Attacks
            • Arp Poisoning
            • Null Sessions
            • Windows Shares
            • Authentication Cracking
          • Networking
            • Python Server to Receive Exfiltrated Data
            • CLI Tool to interact with HTTP/HTTPS
          • Programming
            • C++ Keylogger
            • C++ Information Stealer
          • System Attacks
            • Pivoting
            • Backdoor
          • Vulnerability Assessment
          • Web Attacks
            • SQL Injection
            • HTTP Verbs
            • Web Server Fingerprinting
      • PortSwigger
        • Cross-origin resource sharing (CORS)
          • Access-Control-Allow-Origin response header
          • Vulnerabilities arising from Misconfigurations
            • Server-generated ACAO header from client-specified Origin header
            • Errors parsing Origin headers
            • Whitelisted null origin value
            • Exploiting XSS via CORS trust relationships
            • Breaking TLS with poorly configured CORS
            • Intranets and CORS without credentials
            • Mitigations
        • Sql Injection
          • Examining the database
          • Retrieving data from other database tables
          • Blind SQL injection
          • Mitigation
      • TryHackMe
        • Main Methodology
          • 1. Reconnaissance
            • Google Dorking
            • Metadata Reader/Writer
            • Steghide - Stegnography
            • OSINT Framework
          • 2. Enumeration/Scanning
            • NFS Enumeration Tools
            • NMAP - Port Scanning
            • Web Enumeration Tools
            • SMB Enumeration Tools
            • SMTP Enumeration Tools
            • Shodan - IOT Search Engine
            • FTP Enumeration Tools
            • Wordpress Enumeration Tools
            • OWASP ZAP - WebApp Testing
            • BurpSuite - WebApp Testing
            • MySQL Enumeration Tools
            • Wordlists
          • 3. Gaining Access / Exploitation
            • Buffer Overflow
              • 1. Immunity Debugger
              • 2. Mona Setup
              • 3. Spiking
              • 4. Fuzzing
              • 5. Crash Replication & Controlling EIP
              • 6. Finding Bad Characters
              • 7. Find a Jump Point
              • 8. Generate Payload
              • 9. Prepend NOPs
              • 10. Final Buffer
            • Cryptography
              • Hash Crack Tools
              • Online Password Cracking Tools
              • Encryption
              • John the Ripper
            • Evasion Techniques
            • Shells
              • Powershell
              • Msfvenom
              • Meterpreter
              • Metasploit -- multi/handler
              • Netcat
              • Socat
            • Web Applications
              • OWASP Top 10
              • File Upload Vulnerabilities
              • Authentication Vulnerability
              • XML External Entity (XXE)
              • Cross-Site Scripting (XSS)
              • ZTH: Obscure Web Vulns
              • Server Side Request Forgery (SSRF)
              • Insecure Direct Object Reference (IDOR)
              • ZTH : Continued
              • File Inclusion Vulnerability
                • Local File Inclusion (LFI)
                • Log Poisoning Attack (LFI to RCE via Log files)
            • Windows Applications
              • Jenkins
              • Windows Active Directory
                • Impacket's secretsdump.py
                • Kerberos
                  • Enumerating Users with Kerbrute
                  • Enumerating SPN Accounts with Powershell
                  • Get SPN Account Ticket with Invoke-Kerberoast
                  • Kerberoasting with Rubeus & Impacket
                  • AS-REP Roasting with Rubeus/GetNPUsers.py
                  • Pass the Ticket with mimikatz
                  • Golden/Silver Ticket Attacks with mimikatz
                  • Kerberos Backdoors with mimikatz
                  • Harvesting and Brute-Forcing with Rubeus
                  • Conclusion and Resources
          • 4. Post Exploitation
            • Privilege Escalation
              • Linux
                • 1. Introduction
                • 2. Scripts
                • 3. Kernel Exploits
                • 4. Service Exploits
                • 5. Weak File Permissions
                • 6. Sudo
                • 7. Cron jobs
                • 8. SUID/SGID Executable
                • 9. CAP_SETUID Capabilities Executable
                • 10. Passwords & Keys
                • 11. NFS
                • PrivEsc CTF Checklists
              • Windows
                • Token Impersonation
                • PrivEsc CTF Checklists
                • Permission
                • Scripts
                • Unquoted Service Path
            • Tools
              • Meterpreter Modules
              • Impacket's Psexec
              • Impacket's mssqlclient.py
              • Firefox Decryptor
              • Socat - Reverse TCP Tunnel
            • Windows Active Directory
              • Enumeration with Powerview
              • Enumeration with Bloodhound (GUI)
              • Dumping Hashes with mimikatz
              • Golden Ticket Attacks with mimikatz
              • Enumeration with Server Manager
              • Maintaining Access
              • Additional Resources
          • 5. Covering Tracks
          • 6. Reporting
        • Networking Concepts
          • SSH Reverse Tunnels
        • Scripting
          • Curl
          • Powershell
            • Basic Powershell Commands
            • Enumeration
        • Web Extensions
          • Shodan
          • Wappalyzer
      • Miscellaneous
        • SMTP Enumeration
        • Nmap Advanced Scanning
        • Persistence via Meterpreter
        • DNS Enumeration
        • NetBIOS & SMB
        • DHCP Starvation
        • Packet Manipulation
        • Hash Cracking
        • MITM
        • Msfvenom Payload in APK (Manual Embedding)
    • Blue Teaming
      • Digital Forensics & Incidence Response
        • Memory Acquisition with LIME
        • Disk Analysis with Autopsy
        • Data and Memory Collection with FireEye Redline
        • Memory Forensice with Volatility
      • Intrusion Detection
        • Intrusion Detection Systems (IDS)
        • Threat Monitoring with Security Information & Event Management (SIEM)
        • Security Event Monitoring
        • Host Based Intrusion Detection System (HIDS) - OSSEC
      • Miscellaneous
        • Docker Image Security Analysis with Trivy
  • DevOps
    • Infrastructure as a Code (IaC)
      • Ansible
        • 1. Playbooks
        • 2. Modules
        • 3. Inventory
        • 4. Roles
        • 5. Ansible Tower
      • Terraform
        • 1. Overview
        • 2. Modify Resources
        • 3. Delete Resources
        • 4. Reference Resources
        • 5. Terraform Files
        • 6. Terraform State Commands
        • 7. Terraform Output
        • 8. Target Resources
        • 9. Terraform Variables
        • 10. Expressions + Functions
        • 11. Meta-Arguments
        • 12. Modules
        • 13. Managing Multiple Environments
        • 14. Testing Terraform Code
        • 15. Final Thoughts
    • Orchestration
      • Kubernetes
        • 1. Main K8s Components
        • 2. K8s Architecture
        • 3. Minikube & Kubectl
        • 4. YAML Configuration File
        • 5. K8s Namespaces
        • 6. K8s Ingress
        • 7. Helm - Package Manager of K8s
        • 8. K8 Volumes
        • 9. K8 StatefulSet
        • 10. K8 Services
  • Development
    • Blockchain
      • FreeCodeCamp Course
        • 1. Introduction
        • 2. Solidity Basics
        • 3. Storage Factory
        • 4. Fund Me
        • 5. Ethers.js
        • 6. Hardhat
        • 7. Hardhat | Fund Me
        • 8. Contract Lottery | Raffle
        • 9. IPFS
        • 10. ERC20s
        • 11. DeFi & Aave
        • 12. NFTs | Encoding
        • 13. Reentrancy Attack
    • Backend
      • NodeJs
        • Introduction
        • Additional Concepts
        • ExpressJs
    • Database
      • SQL
        • Basics
          • 1. Querying Data
          • 2. Filtering Data
          • 3. Joining Multiple Tables
          • 4. Grouping Data
          • 5. Set Operations
          • 6. Grouping Sets, Cube, and Rollup
          • 7. Subquery
          • 8. Common Table Expressions
          • 9. Modifying Data
          • 10. Transactions
          • 11. Import & Export Data
          • 12. Managing Tables
    • Testing
      • Test Driven Development (TDD)
      • Jest js
      • Cypress js
Powered by GitBook
On this page
  • Grouping Sets
  • Cube
  • Rollup
  1. Development
  2. Database
  3. SQL
  4. Basics

6. Grouping Sets, Cube, and Rollup

Previous5. Set OperationsNext7. Subquery

Last updated 1 year ago

Grouping Sets

(generate multiple grouping sets in reporting)

  • It a set of columns by which you group by using the GROUP BY clause

  • It is denoted by a comma separated list of columns placed inside parenthesis i.e (column1, column2, ...)

Example

The sales table stores the number of products sold by brand and segment

To get number of products sold by brand and segment

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand,
    segment;

To get number of products sold by a brand

SELECT
    brand,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand;

To get number of products sold by a segment

SELECT
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment;

To get number of products sold for all brands and segments

SELECT SUM (quantity) FROM sales;

To get all of above in a single query

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand,
    segment

UNION ALL

SELECT
    brand,
    NULL,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand

UNION ALL

SELECT
    NULL,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment

UNION ALL

SELECT
    NULL,
    NULL,
    SUM (quantity)
FROM
    sales;
  • Because UNION ALL requires all result sets to have the same number of columns with compatible data types, you need to adjust the queries by adding NULL to the selection list

  • it has two main problems.

    1. First, it is quite lengthy.

    2. Second, it has a performance issue because PostgreSQL has to scan the sales table separately for each query.

Using the GROUPING SETS clause

SELECT
	brand,
	segment,
	SUM (quantity)
FROM
	sales
GROUP BY
	GROUPING SETS (
		(brand, segment),
		(brand),
		(segment),
		()
	);

Grouping function

GROUPING() function returns bit 0 if argument is member of current grouping set and 1 otherwise

SELECT
	GROUPING(brand) grouping_brand,
	GROUPING(segment) grouping_segment,
	brand,
	segment,
	SUM (quantity)
FROM
	sales
GROUP BY
	GROUPING SETS (
		(brand),
		(segment),
		()
	)
ORDER BY
	brand,
	segment;

Cube

(define multiple grouping sets that include all possible combinations of dimensions)

  • It allows us to generate multiple grouping sets

  • CUBE subclause is a short way to define multiple grouping sets so the following are equivilant

CUBE (c1, c2, c3)

GROUPING SETS (
	(c1, c2, c3),
	(c1, c2),
	(c1, c3),
	(c2, c3),
	(c1),
	(c2),
	(c3),
	()
)
  • In general, if the number of columns specified in CUBE is n, then we will have 2n combinations

Example

Consider the sales table

The following query uses CUBE subclause to generate multiple grouping sets

SELECT
	brand,
	segment,
	sum (quantity)
FROM
	sales
GROUP BY
	CUBE (brand, segment)
ORDER BY
	brand,
	segment;

Rollup

(generate reports that contain totals and subtotals)

  • It is different from CUBE subclause, ROLLUP does not generate all possible grouping sets based on specified columns, it just makes a subset of those

  • It assumes a heirarchy among the input columns and generates all grouping sets that make sense considering the heirarchy. This is the reason why ROLLUP is often used to generate the subtotals and grand total for reports

  • Example, CUBE (c1, c2, c3) makes all eight possible grouping sets

(c1, c2, c3)
(c1, c2)
(c2, c3)
(c1, c3)
(c1)
(c2)
(c3)
()
  • However, ROLLUP (c1, c2, c3) generates only four grouping sets, assuming the heirarchy c1 > c2 > c3 as follows

(c1, c2, c3)
(c1, c2)
(c1)
()
  • Common use of ROLLUP is to calculate aggregations of data by year, month and date, considering the heirarchy year > month > date

  • It is also possible to do a partial roll up to reduce the number of subtotals generated.

SELECT
    c1,
    c2,
    c3,
    aggregate(c4)
FROM
    table_name
GROUP BY
    c1,
    ROLLUP (c2, c3);

Example (number of products sold by brand (subtotal) and by all brands and segments (total))

SELECT
	brand,
	segment,
	SUM (quantity)
FROM
	sales
GROUP BY
	ROLLUP (brand, segment)
ORDER BY
	brand,
	segment;

If you change the order of brand and segment, the result will be different as follows:

SELECT
    segment,
    brand,
    SUM (quantity)
FROM
    sales
GROUP BY
    ROLLUP (segment, brand)
ORDER BY
    segment,
    brand;

The following statement performs a partial roll-up:

SELECT
    segment,
    brand,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment,
    ROLLUP (brand)
ORDER BY
    segment,
    brand;

Example (finds the number of rental per day, month, and year)

This is rental table

SELECT
	EXTRACT (YEAR FROM rental_date) y,
	EXTRACT (MONTH FROM rental_date) m,
	EXTRACT (DAY FROM rental_date) d,
	COUNT (rental_id)
FROM
	rental
GROUP BY
	ROLLUP (
		EXTRACT (YEAR FROM rental_date),
		EXTRACT (MONTH FROM rental_date),
		EXTRACT (DAY FROM rental_date)
	);