CODEWITHSUNDEEP
javasqlspringspring-bootjpa
ali
ali

Reputation: 11

Count my customer in one Query In Spring boot

I have a table in which I store customer information such as customer name and type like this

id name type
1 c1 Gold
2 c2 Silver
3 c3 Bronze
4 c4 Usual
5 c5 Gold
6 c6 Gold
7 c7 Silver

And need to find the number of each type of customer

For example, number of gold customers? Gold = 3 , Silver = 2 , Bronze = 1 , Usual = 1

Can anyone solve my problem?

@Query("SELECT COUNT(p.gold) AS gold , COUNT(p.silver) AS silver , COUNT(p.bronze) AS bronze , COUNT(p.usual) AS usual  from Customer p")
MyModel selectTotals();

Upvotes: 0

Views: 224

Answers (2)

user12138732
user12138732

Reputation:

You can try with STUFF and Group By, If you want the results in one row

SELECT result = (STUFF((
select ', ' + type + '=' + cast(count(*) as varchar)
from Customer 
group by type
having count(*)>5
        FOR XML PATH('')
        ), 1, 2, '')
    )

Upvotes: 0

eshirvana
eshirvana

Reputation: 24593

here is how your sql query should look like:

SELECT 
    COUNT(case when p.type= 'gold' then 1 end) AS gold 
  , COUNT(case when p.type= 'silver' then 1 end) AS silver 
  , COUNT(case when p.type= 'bronze' then 1 end) AS bronze 
  , COUNT(case when p.type= 'usual' then 1 end) AS usual  
from Customer p

Upvotes: 1

Related Questions