sql count function with subquery

Question

here is my query

select narr,vocno,count(*) 
from KontenLedger 
WHERE VOCDT>'2018-07-01' 
group by narr,vocno 
having count(*)<'3'

actually if i wright as i given above ,the result which calculates two fields ('narr' and 'vocno') if i remove the field ('narr') answer is correct. i need to view the field 'narr' also without counting

Please Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. edit your question - do not post code or additional information in comments. — user330315
– user330315, Commented Jul 28, 2018 at 10:36
Unrelated tip: you are comparing the numeric count value against a string '3'. If you remove the quotes, SQL won't have to implicitly convert it. — Richardissimo
– Richardissimo, Commented Jul 28, 2018 at 11:12
In addition to the aforementioned by a_horse_with_no_name and Richardissimo, it's worthwhile to clarify the database used. — Andrei Odegov
– Andrei Odegov, Commented Jul 28, 2018 at 11:29

LukStorms · Accepted Answer · 2018-07-28 20:44:39Z

1

Without knowing your database, nor having some limited sample date, nor expected output?

SELECT
 vocno, 
 COUNT(*) AS total,
 MAX(narr) AS max_narr
FROM KontenLedger 
WHERE vocdt > '2018-07-01' 
GROUP BY vocno 
HAVING COUNT(*) < 3

answered Jul 28, 2018 at 20:44

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LukStorms Over a year ago

@nashvamuhammed Really? Okay that's good. Thought it was going to need some database specific function. Like f.e. STRING_AGG for MS Sql Server. But I was probably just overthinking it.

Collectives™ on Stack Overflow

sql count function with subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related