nesting a count in SQL
51 观看
2回复
37 作者的声誉
I am trying to count the distinct number of stores (stores 1-10) that have a certain sku. Here's my code:
SELECT distinct COUNT(*) as total_store
FROM(
select distinct st.*
from (select st.*
from store_table st
)st
WHERE st.store between 1 and 10
AND st.sku = 10101
GROUP BY st.store
HAVING COUNT(*) >= 1
)a;
I keep getting an error:
ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"
and I am not sure why it is saying that?
作者: Alex Fields 的来源 发布者: 2017 年 12 月 27 日回应 2
3像
43050 作者的声誉
Your error is cased by you grouping by st.store
without selecting the data. All non-aggregates that you want in either your SELECT or GROUP BY clauses must appear in both.
Having said that, you're making this far too complicated. If we start at the beginning you want something from stores:
select * from stores
only certain stores
select * from stores where store between 1 and 10
with a certain SKU
select * from stores where store between 1 and 10 and sku = 10101
now you want the unique stores
select distinct store from stores where store between 1 and 10 and sku = 10101
finally you want the count of the unique stores
select count(distinct store)
from stores
where store between 1 and 10
and sku = 10101
It looks like you're building your queries up from layers of sets rather than layers of logic. A query is a set of conditions that only requires the creation of sets (inline view etc.) if you need to add an operation that relies on a constructed expression. When building queries focus on layering additional logic. If the resultant query seems too complicated when compared to how you would say it then there's a possibility that it is.
Based on the table name STORES I'd actually assume that it was unique on STORE (and that this was the primary key and therefore non-null) and so you don't need to perform the additional sort to make it unique
select count(*)
from stores
where store between 1 and 10
and sku = 10101
作者: Ben
发布者: 2017 年 12 月 27 日
0像
14 作者的声誉
In SQL when you are use the group by then you must need to define the select Fields you can't use * and only one Fields with group by. Yes you can use * then you must need to declare all Fields in group by. Also you need to put all Fields with aggregation function but whichever Fields are with group by is not allowed to use aggregation.
作者: Savaliya Bhavesh 发布者: 2017 年 12 月 27 日来自类别的问题 :
- sql 检查SQL Server表的更改?
- sql 数据库索引如何工作?
- sql 如何索引数据库列
- sql 删除名称以某个字符串开头的所有表
- sql 应该有人决定切换数据库系统
- oracle 如何在SQL数据库表中选择第n行?
- oracle 在Oracle中进行多行插入的最佳方法是什么?
- oracle 您要查询哪些表/视图以选择Oracle中架构中的所有表名?
- oracle 如何解决ORA-011033:正在进行ORACLE初始化或关闭
- oracle 如何在不同的表空间中导入oracle转储
- group-by 如何通过'查询连接PostgreSQL'组中的字符串字段的字符串?
- group-by GROUP BY和DISTINCT有什么区别
- group-by MySQL查询GROUP BY日/月/年
- group-by 按多列分组
- group-by 选择3条最新记录,其中一列的值是不同的
- having HAVING和WHERE有什么区别?
- having MySQL-在WHERE子句中使用COUNT(*)
- having 用于查找count> 1的记录的SQL查询
- having PostgreSQL在哪里计算条件
- having 在UPDATE语句中使用HAVING子句