How to use count "how many times a particular customer made inquiries"?

Question

I have this table called "Anfrage". And these are the columns in "Anfrage Table":

Anfrage Table

I want to count how many times the same customer made inquiries. So I made a new column "Menge_Anfrage" which means number of inquiries. The result should be like this:

Table Result

I tried to use this query:

 `SELECT DISTINCT Count(*)
FROM Anfrage
WHERE KundenNr = 1
) AS "Menge Anfrage",`

but it returns a "4" in all rows in "Menge Anfrage" column. Could someone please tell me the right query? I'm using SQL Tools 1,8 b38. Thank you so much.

Answer 1

It looks like you want to count the number of times KundenNr occurs. Your table doesn't need a new column added for this; you should calculate it every time in the query:

SELECT
  KundenNr,
  Project,
  COUNT(*) OVER(PARTITION BY KundenNr) as Menge_Anfrage
FROM
  Anfrage

The query above is the equivalent of this query, that works in databases thatdo not support analytical functions:

SELECT
  a.KundenNr,
  a.Project,
  x.Menge_Anfrage
FROM
  Anfrage a
  INNER JOIN (SELECT KundenNr, COUNT(*) as Menge_Anfrage FROM Anfrage GROUP  BY KundenNr) x 
  ON a.KundenNr = x.KundenNr

OVER(PARTITION BY KundenNr) does pretty much what the bottom query's GROUP BY subquery does; it breaks the dataset into partitions (groups) based on different values of KundenNr and the COUNT(*) applies to the partition, not the whole dataset. Study and understand how the bottom query works, and then you should be able to understand the top one if you bear in mind that the join between a partitioned kundennr and the row from the table, is automatic.

Here's another way of writing the same thing:

SELECT
  a.KundenNr,
  a.Project,
  (SELECT COUNT(*) FROM Anfrage x WHERE x.KundenNr = a.KundenNr) as Menge_Anfrage
FROM
  Anfrage a

You could consider that oracle will, for every row in the output, run that subquery presented in the select. This is logically the equivalent of running a grouping query and then joining the results, or creating a partition hash table that maps KundenNr to Count and then matching them up when preparing the results