0

I am using the below query to get distinct records from 4 specific columns in an sql DB.

SELECT DISTINCT customer,
       product,
       category,
       sector
FROM data_table

I need to add the count of products in this query. Any ideas?

Cid
  • 14,968
  • 4
  • 30
  • 45
Harris Vrachimis
  • 1
  • 4
  • 1
    What do you mean `count of products`? What database are you using? In databases with window functions you could use `OVER(*)` with `COUNT()` or `DENSE_RANK()` [like these answers](https://stackoverflow.com/questions/11202878/partition-function-count-over-possible-using-distinct) to perform the equivalent of `COUNT(DISTINCT product)`. If you want the overall totall, `COUNT(*) OVER()` or `OVER(ORDER BY (SELECT NULL)) ` – Panagiotis Kanavos Dec 11 '19 at 10:36
  • 1
    Can you please show sample data and expected results? As it stands, it's hard to answer your question...Please also specify the specific RDBMS you're using. – Neville Kuyt Dec 11 '19 at 11:03
  • @HarrisVrachimis . . . What do *you* mean by "add the count of products in this query"? It is not at all clear what the result set would look like. – Gordon Linoff Dec 11 '19 at 11:06

1 Answers1

2

are you find something below

select count(*) from
(SELECT DISTINCT customer, product, category, sector 
FROM data_table
) a

or do you need window function count() if your dbms support

SELECT DISTINCT customer, product, category, sector,
count(*) over()  as cnt
FROM data_table
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63