query problem (group by)

• tutufool

  Valued Member

  Points: 56

  More actions

  November 14, 2006 at 6:09 am

  #115079

  I have tables like:

  username         userType

  user1               A

  user2               A

  user3               B

  user4               C

  I want one signle query to return

  userType          userCount            percent

  A                        2                      50%

  B                         1                      25%

  C                          1                     25%

   

  can anyone help me ?

  thanks

   

• AMIT GUPTA-263376

  SSCarpal Tunnel

  Points: 4244

  More actions

  November 14, 2006 at 6:28 am

  #671590

  Hi,

  use this query. It will help you to find out the desired result :

  Select usertype,count(*) userCount ,

  (count(*) * 100 / (select count(*) from table_a)&nbsp [percent]

  from table_a

  group by   usertype

  thanks...

  Amit Gupta    

• EddieBaquet

  SSC Veteran

  Points: 267

  More actions

  November 14, 2006 at 6:32 am

  #671592

  Hi, Amit.  Nice clean solution.  The only issue you might run into is making the [precentage] a decimal.

  (count(*) * 100 / cast((select count(*) from table_a) as decimal(5,2)) [percent]

  Eddie

  
  
  "If you don't take the time to do it right, when are you going to find the time to do it over?"

• tutufool

  Valued Member

  Points: 56

  More actions

  November 14, 2006 at 6:42 am

  #671600

  thank you all for so quick reponse.

  one more question, if the table is not so simple, it's a big query

  is there any way to avoid repeating the query in the child query?

  just like:

  Select usertype,count(*) as userCount ,

  (count(*) * 100 / (select count(*) from temp_table)  [percent]

  from

  (select usertype, xxx,xxx, from table1

  inner join table1

  ....

  ....

  group by  usertype) as some temp_table

  I just dont' want to repeat the huge query twice, any ideas?

   

   

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  November 14, 2006 at 6:59 am

  #671607

  is there any way to avoid repeating the query in the child query

  Only by using a temp table, either

  Extract the detail to a temp table

  Save the @@ROWCOUNT value

  Summarise the temp table and use the saved @@ROWCOUNT value

  Extract the COUNT(*) summary into temp table

  Sum the COUNT values into a variable

  SELECT from the temp table and use the variable

  Far away is close at hand in the images of elsewhere.
  Anon.