0
    SELECT u.shopid,i.item_stock,i.item_ctime,
        Case
            When i.item_stock < 10 then 'Group 0'
            when i.item_stock between '10' and '99' then 'Group 1'
            when i.item_stock between '100' and '999' then 'Group 2'
            when i.item_stock >= 1000 then 'Group 3'
        Else 'None'
    End as Grouping
  FROM [dfgsdfg].[dbo].[user_profile$] as u
  join item_profile$ as i
  on i.shopid = u.shopid
  where (i.item_ctime between '2017-01-01' and '2017-05-31')

This is the result I get after I have tried many queries image

But the desired table would be like this

    GROUP       Number of shops
    0           xxx
    1           xxx
    2           xxx
    3           xxx

How do I get a table like this i have tried and finding about this for a while now

Thom A
  • 88,727
  • 11
  • 45
  • 75
Kanthaphong Tachapong
  • 1
  • 1
  • It seems you know you need to use a `GROUP BY` here, but you haven't actually used one. It seems like none of the columns apart from the one you've aliased `Grouping` are relevant here. What you want is that `Grouping` column, a `COUNT` and then the appropriate `GROUP BY`. – Thom A Jan 14 '22 at 14:01
  • @Larnu I have tried to [group by] using the [grouping] column but it said [invalid column named grouping] which I have created through case function – Kanthaphong Tachapong Jan 14 '22 at 14:04
  • Because you can't reference a column by it's alias (defined in the `SELECT`) in the `GROUP BY`; you need to repeat the expression or use a devired table. – Thom A Jan 14 '22 at 14:07
  • Does this answer your questioner? [SQL - using alias in Group By](https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by) – Thom A Jan 14 '22 at 14:10
  • I'm still trying to figure out, I'm new to SQL so its pretty confusing it would be great if I could see how to write it. Thank you – Kanthaphong Tachapong Jan 14 '22 at 14:18
  • select case...end as grouping, count(shopid) as [number of shops] from yourtable group by case..end – GuidoG Jan 14 '22 at 14:19
  • Do i have to edit this in my query or create another subquery – Kanthaphong Tachapong Jan 14 '22 at 14:24
  • I have manage to figure it out now thank you – Kanthaphong Tachapong Jan 14 '22 at 14:27
  • Does this answer your question? [Use case expression to get count and percentage for each condition](https://stackoverflow.com/questions/69701126/use-case-expression-to-get-count-and-percentage-for-each-condition) – Charlieface Jan 15 '22 at 19:25

0 Answers0