CODEWITHSUNDEEP
sqlsql-servert-sqlselect
Anil
Anil

Reputation: 15

How to sum user amount who visited my shop more than once

I am trying to sum the total number of customers who visited more than once per year. If I use any filter like count(cst_mobile)>2 SQL asking me to use having. I can find as below visit amounts (well, sort of, I manually sum customer amount who visited more than once) but can't find how to sum user amount only who visited more than once. Below you can see the result I find now.

Date    Number of Visits    Customer Amount
2020           1                1532
2020           2                2258
2020           3                804
2020           4                166
2020           5                56


Which part of this query should I edit to see the only sum amount?

    select LEFT(CONVERT(datetime2, date_visit, 120), 4) as 'Date',num_visits as 'Number of Visits',count(cst_mobile) as 'Customer Amount'
from (
      select LEFT(CONVERT(datetime2, datevisit, 120), 4) as date_visit,v.cst_mobile
      , count(*) as num_visits
      from users v
      where class_user='Premium' and cst_id != 0 and cst_mobile not like '%000000%'
      and cst_mobile!=''
      group by  LEFT(CONVERT(datetime2, datevisit, 120), 4),cst_mobile
     ) v
group by LEFT(CONVERT(datetime2, date_visit, 120), 4),num_visits
order by LEFT(CONVERT(datetime2, date_visit, 120), 4)desc, num_visits asc```

Upvotes: 0

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Why are you going through such convolutions to get the year? The subquery can look like this:

  select year(datevisit) as datevisit_year, v.cst_mobile,
         count(*) as num_visits
  from users v
  where class_user = 'Premium' and
        cst_id <> 0 and
        cst_mobile not like '%000000%' and
        cst_mobile <> ''
  group by year(datevisit), cst_mobile
  having count(*) >= 2;

And the outer query like:

select datevisit_year, num_visits, count(*)
from (select year(datevisit) as datevisit_year, v.cst_mobile,
             count(*) as num_visits
      from users v
      where class_user = 'Premium' and
            cst_id <> 0 and
            cst_mobile not like '%000000%' and
            cst_mobile <> ''
      group by year(datevisit), cst_mobile
      having count(*) >= 2
     ) yc
group by datevisit_year, num_visits
order by datevisit_year, num_visits;

Upvotes: 3

Related Questions