nesting a count in SQL
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 日
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
作者: Ben 发布者: 27.12.2017 05:57
select count(*) from stores where store between 1 and 10 and sku = 10101
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 发布者: 27.12.2017 06:21