0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for administrators and users of the CiviCRM Constituent Relationship Management software

--select user coming from china and india ang usa
--after checking the logical model, the user table has all infromation we need--
/*
select * 
from Users
where location like '%China5'
    or 
    location like '%India%'
    or 
    location like '%usa%';
    */
--rewrite by using case then--

/*
select Id, Location, 'country coming from' = 
case when location like '%India%' then 'coming from India'
     when Location like '%China%' then 'coming from China'
     when Location like '%USA%' then 'coming from usa'
     when Location is null then 'no location information'
     else 'coming from other countries'
     end
from Users


select*
from users
*/
/*
--generte a frequency table from reputation --
select count(Id) as 'countReputation', Reputation
from Users
group by Reputation 
having Reputation > 100 
order by Reputation desc
*/

--find users whos reputation are above average --
--first step: find average reputation
select avg(Reputation)
from Users
--second step: find the users who's reputation are above average--
select Id
from Users
where Reputation > 95

fork query

edited mar 27

created mar 20 anonymous