Count occurence of distinct value without grouping?

Question

I need a line-by-line extract without grouping all clientID's, see below.

I'm writing a quick extract to find how many times our customers have booked a product so we can see what their second, 3rd or 4th booking is.

select c.clientid, c.bookref, left(c.tourno, 6) as tourref, c.book_date
from cust c

where c.book_date between '2018-01-01' and ' 2020-01-01'

order by clientid asc, book_date asc

This is pretty basic SQL and it turns a result with one line per booking. What I need to do is count the number of times that ClientID appears, and by ordering the table by "clientid asc, book_date asc", I should be able to come up with a long list of bookings and what booking number corresponds to each line.

Unfortunately, all the help examples I've seen basically group the ID column in, so I can see that ID "255253" has a count of 7 (and thus 7 bookings), but no idea what those bookings are.

Thank you! I'm using Advantage SQL by the way.

I think the best way to explain it is, imagine you have an ascending list of clientID's in date order in column A on excel, then you apply this formula to cell B2:

=IF(A2=A1,B1+1,1)

cool username and decent first question: upvoted. c'mon back to SOF! — WestCoastProjects, Nov 13 '22 at 07:03

score 1 · Answer 1 · answered Apr 28 '20 at 14:30

1

You can use window functions:

select c.clientid, c.bookref, left(c.tourno, 6) as tourref, c.book_date,
       count(*) over (partition by c.clientid) as client_cnt
from cust c
where c.book_date between '2018-01-01' and '2020-01-01'
order by clientid asc, book_date asc;

AdvantageSQL may not support those. You may be able to concatenate together the things you want:

select c.clientid, count(*) as client_cnt,
       group_concat(c.bookref order by book_date)
from cust c
where c.book_date between '2018-01-01' and '2020-01-01'
group by clientid asc;

answered Apr 28 '20 at 14:30

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, you're right I don't think Advantage supports Window functions, I get an error message that states: poQuery: Error 7200: AQE Error: State = 42000; NativeError = 2115; [iAnywhere Solutions][Advantage SQL Engine]Expected lexical element not found: FROM You are missing the keyword FROM after the column definitions in your SELECT statement. -- Location of error in the SQL statement is: 95 (line: 2 column: 22) – RockandGrohl Apr 28 '20 at 14:43

Peter Szalay · Answer 2 · 2020-04-29T14:44:24.213

1

select c.clientid, c.bookref, left(c.tourno, 6) as tourref, c.book_date, c2.number_of_bookings 
        from cust c
        inner join (select clientid, count(*) AS number_of_bookings from cust
where book_date between '2018-01-01' and '2020-01-01'        
group by clientid) c2
        on c.clientid = c2.clientid 
        order by c.clientid asc, c.book_date asc

I would join the same table to itself.

edited Apr 29 '20 at 14:44

answered Apr 28 '20 at 14:43

Peter Szalay

376
3
13

Hi Peter, thanks for this, it works great. There is just one more thing, so this correctly shows the number of occurences, but it doesn't show a rising number to show which booking each set of clientID's is. Put this way, say we have ClientID 6 and Client 7, 6 has two bookings and 7 has three. I'd like to see a 1 and 2 next to each client's line, so their first booking is "1" and their second is "2" - and likewise for client 7, who would have a 1, 2 and 3 on each respective line. Hope this makes sense. – RockandGrohl Apr 29 '20 at 13:32
Actually, sorry, comparing this to a countif of the extract produces 95% accuracy. I think your join is comparing a count of the clientID to the whole database unfortunately, rather than the timeframe. – RockandGrohl Apr 29 '20 at 13:52

Count occurence of distinct value without grouping?

2 Answers2