Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Count from two tables

Following situation:
Table 1:
Emp_ID
Emp_Name


Table 2:
Trans_ID
Emp_ID



Goal:
I need a report that displays ALL records from Table 1 and displays then number of related records in Table 2.
It should also show records from Table 1 if there are NO records in Table 2. (i.e. result would be Employee Nr. 712 = 0 transactions)

How do I do this in Crystal Reports?
DB is DB2.
Do I need to make queries for this, or can I count in the report itself?
How would the queries look like?

THANKS A LOT
0
olio
Asked:
olio
  • 3
  • 2
  • 2
  • +2
5 Solutions
 
Bill BachPresidentCommented:
Two ways, depending on the capabilities of the SQL engine:

SELECT Emp_ID, Emp_Name, (SELECT COUNT(*) FROM Table2 WHERE Table2.Emp_ID = Table1.Emp_ID) FROM Table1

SELECT Emp_ID, Emp_Name, COUNT(*) FROM Table1
LEFT OUTER JOIN Table2 WHERE Table2.Emp_ID = Table1.Emp_ID
GROUP BY Emp_ID, Emp_Name
0
 
momi_sabagCommented:
i would use BillBach second suggestion
the first one is very bad in terms of perfromance if you have many rows
it's query needs a slight modification:

SELECT Emp_ID, Emp_Name,  sum(case t2.Emp_ID when null then 0 else 1 end)
FROM Table1 t1
LEFT OUTER JOIN Table2  t2 WHERE t2.Emp_ID = t1.Emp_ID
GROUP BY Emp_ID, Emp_Name

this is the correct
his query will return 1 if no rows exist for a given id, and not 0 as you requested
0
 
momi_sabagCommented:
if the above returns sytax error then try

SELECT Emp_ID, Emp_Name,  sum(case when t2.Emp_ID is null then 0 else 1 end)
FROM Table1 t1
LEFT OUTER JOIN Table2  t2 WHERE t2.Emp_ID = t1.Emp_ID
GROUP BY Emp_ID, Emp_Name

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
olioAuthor Commented:
can i use this right in Crystal Report?
I have no direct access to DB2.
I can just create reports.
This is the difficulty in the organziational setup.
0
 
momi_sabagCommented:
you can create a view in db2 that runs the abobe query and then you can just query that view as if it was a regular table
0
 
mlmccCommented:
Yes, you can add the SQL as a Crystal COMMAND when you set the database source.

If you prefer there are ways to get the counts in Crystal.

mlmcc
0
 
olioAuthor Commented:
I prefer to count in Crystal an not with an SQL command.
How would that be done mlmcc?
0
 
mlmccCommented:
The SQL command will be faster than in Crystal.

There are a couple of ways of counting in Crystal.
Easy way if you are counting all records
Right click a field in the report
Click INSERT --> SUMMARY
Choose type as COUNT
CHoose the appropriate group or report footer.

If you are trying to count distinct values use DISTINCT COUNT

You can also use a variable or running total to do the counting.
If summaries don't work I'll explain those.

mlmcc
0
 
James0628Commented:
As mlmcc said, you can use a Count summary.

 Of course you also need to get your tables in the report, and in case you're not sure how to do that ...

 Add the tables to the report, link Table1 to Table2 on Emp_ID (or whatever fields are appropriate) and make the link a LEFT OUTER JOIN, so that records from Table1 will be included even if there are no corresponding records in Table2.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now