Question About Counting RSS

• • Reply
  kumper33

  None

  0 Points

  2 Posts

  Question About Counting

  Apr 25, 2018 05:30 PM|kumper33|LINK

  Still learning crystal reporting but have a question:

  I have three columns of data. Author 1, Author 2 and Author 3. A workers name will be in any of these columns for multiple entries. How would I go about counting them and having a report at the end. I do not have a database of names to compare them to. The data would look like. Any help would be great as I need a kick in the right direction. Thank you!

  Item     Author 1     Author 2     Author 3   
  1        Bob          Tom          Larry  
  2        Larry        Tom          Bob
  

  I would need a report that spits out:

  Name    Total  
  Bob     2  
  Tom     2  
  Larry   2 

• • Reply
  oned_gk

  All-Star

  52433 Points

  15637 Posts

  Re: Question About Counting

  Apr 26, 2018 12:22 AM|oned_gk|LINK

  SELECT name, COUNT(*) as Total FROM (
  SELECT Autor1 as name FROM tbl
  UNION ALL
  SELECT Autor2 as name FROM tbl
  UNION ALL
  SELECT Autor3 as name FROM tbl
  ) as tblall group by name

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  kumper33

  None

  0 Points

  2 Posts

  Re: Question About Counting

  Apr 26, 2018 03:59 PM|kumper33|LINK

  do I need to declare "name" as a variable or?

  and thanks for the help!

• • Reply
  oned_gk

  All-Star

  52433 Points

  15637 Posts

  Re: Question About Counting

  Apr 27, 2018 01:28 AM|oned_gk|LINK

  kumper33

  do I need to declare "name" as a variable or?

  and thanks for the help!

  No

  Name is column alias for autorX

  SELECT autor1 as Name from yourtable result is like this

  Name
  Bob
  Larry

  Also autor2, autor3 similar above result, then UNION ALL (query1,query2,query3) RESULTS is like this

  Name
  Bob
  Larry
  Tom
  Tom
  Larry
  Bob

  Now, above result we call tblall, then requery above result select name, count(*) as total from tblall group by name

  Above is sql result for crystal datasource, in crystal report only have name and total field

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer