locked
Question About Counting RRS feed

  • Question

  • User1025900490 posted

    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 
    Wednesday, April 25, 2018 5:30 PM

All replies

  • User-1716253493 posted
    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

    Thursday, April 26, 2018 12:22 AM
  • User1025900490 posted

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

    and thanks for the help!

    Thursday, April 26, 2018 3:59 PM
  • User-1716253493 posted

    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

    Friday, April 27, 2018 1:28 AM