[Postgres] Group and Aggregate Data in Postgres
in and Data group Postgres aggregate
2023-09-14 08:59:18 时间
How can we see a histogram of movies on IMDB with a particular rating? Or how much movies grossed at the box office each month? Or how many movies there are of each genre? These are examples of data aggregation questions, and this lesson will teach us how to answer them.
In the table we have 'action', 'animation'... 'short' categories. They all use 'true' of 'false'.
What if we want to use those by its categoreis not just ture of false?
We can use 'CASE' in Postgres.
SELECT CASE WHEN action=true THEN 'action' WHEN animation=true THEN 'animation' WHEN comedy=true THEN 'comedy' WHEN drama=true THEN 'drama' WHEN short=true THEN 'short' ELSE 'other' END AS genre, title FROM movies LIMIT 100
And now we want to get "how many movies for each category" from previous result.
What we can do is using "GROUP BY" and "WITH":
WITH genres AS( SELECT CASE WHEN action=true THEN 'action' WHEN animation=true THEN 'animation' WHEN comedy=true THEN 'comedy' WHEN drama=true THEN 'drama' WHEN short=true THEN 'short' ELSE 'other' END AS genre, title FROM movies LIMIT 100 ) SELECT genre, COUNT(*) FROM genres GROUP BY genre;
相关文章
- 【错误记录】GitHub 提交报错 ( OpenSSL SSL_connect: SSL_ERROR_SYSCALL in connection to github.com:443 )
- ORA-19728: data object number conflict between table string and partition string in table string ORACLE 报错 故障修复 远程处理
- ORA-39926: Join index between table string.string in tablespace string and table string.string in tablespace string. ORACLE 报错 故障修复 远程处理
- ORA-54507: duplicate points in multipoint geometry ORACLE 报错 故障修复 远程处理
- ORA-15504: cannot start workload capture because instance string not present in RESTRICTED SESSION mode ORACLE 报错 故障修复 远程处理
- sql语句中条件查询in、like、=的效率详解程序员
- MySQL删除IN操作: 简易指南(mysql删除in)
- in Neo4j查询:使用Not In操作(neo4j查询not)
- Working with EXIT, STOP, CONTINUE and RETURN in SAP ABAP详解编程语言
- Linux in命令报错:深入探究(linuxin命令报错)
- MySQL中使用IN子查询的技巧(mysql子查询in)
- MySQL查询优化:从IN中获取更高性能(mysql查询优化in)
- Exploring the Benefits and Uses of PPPD in the Linux Environment(pppdlinux)
- Managing Large Data Volumes in MySQL: Best Practices and Tips(mysql的数据量)
- Mastering the Left Join Function in Oracle: Tips and Tricks(oracleleft)
- 如何有效运用SQL Server的IN语句(sqlserver用in)
- MySQL中如何使用in语句进行筛选(mysql中使用in)
- 深入探究Mysql中IN与AND逻辑运算的应用(mysql中in与and)
- 深入理解Oracle中IN运算符的用法(oracle中的in用法)
- 条件Oracle查询使用两个IN条件(oracle 两个in)
- 使用Oracle IN多表进行联接查询(oracle in多表)
- acle joinOracle IN与Oracle Join的差异探讨(oracle in与or)