nesting a count in SQL

sql oracle group-by having

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 发布者: 27.12.2017 05:57

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 发布者: 27.12.2017 06:21
32x32