Emulate a Crystal Report using SQL Query.

I have a Crystal report with one field based on a Subreport, in the subreport I do a count and only pull that COUNT  into the main report, replacing the whole report with SQL is straightforward, but I'm questioning my approach to replacing the subreport part with a subquery as the results I got this far are sketchy. How would you approach it?
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
The question is not specific enough to narrow down the myriad of choices, here's two...

1. use a subquery

select c1, c2, c3, sq.col1
from table1 t
inner join ( select id, count(*) col1 from sometable group by id) sq on t.id = sq.id

2. or a correlated subquery (less efficient)

select c1, c2, c3, ( select count(*) from sometable st where t.id = st.id) col1
from table1 t
slightwv (䄆 Netminder) Commented:
We did something similar with Crystal.  We pulled back the entire count on every row using the window function and ignored the value in the main report.

select c1, c2, c3, count(c3) over () total_count from sometable;

You can extend it if necessary using CASE.  You only want the 'Y' values?
select c1, c2, c3, count(case when c3 = 'Y' then 1 end) over () y_count from sometable;
PortletPaulEE Topic AdvisorCommented:
ah yes, of course, window functions are are damn fine choice
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Roberto Madro R.Programmer AnalystAuthor Commented:
To help explain better I'm including this graphic, I can select & manipulate info from the "DeliveryTable" & "CustomerInfoTable" and what have you, but I want to embed the total number of Orders by counting them and by Grouping On the CustomerID from the OrderMasterTable, then plug that number in my query of the other tables.Tables
PortletPaulEE Topic AdvisorCommented:
Then I suggest the subquery approach

select t.CustomerID , t.c2, t.c3, sq.numcust
from DeliveryTable t
inner join ( select CustomerID , count(*) numcust from OrderMasterTable group by CustomerID ) sq on t.CustomerID = sq.CustomerID
slightwv (䄆 Netminder) Commented:
Instead of a picture, can you simplify what you need into a small test case with data and expected results (as text, I can't copy and paste from a picture)?

Then we can provide tested code based on your test case.

Until we get a test case, you can use the window function to "group" as well.  It is called PARTITION.

select c1, c2, c3, count(c3) over (partition by customerid) total_count from sometable;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Does this have anything to do with Crystal Reports other than you are trying to match the results of an existing report?

Do you want a query you can use as a Command in Crystal?

Roberto Madro R.Programmer AnalystAuthor Commented:
No mlmcc, my crystal runs fine, except it's far reaching and takes long time to finish running, that's why I opted to base it on a sql query.
Roberto Madro R.Programmer AnalystAuthor Commented:
Many thanks experts, the collective input from both was valuable even if I didn't use either of your solutions for this exact project, I, however used an old script you slightwv helped me on, and it's goes as follows;

A As(
        select column A, column B, from table X,  where X = Y,  Group By XX...),
B As(
        select column C, column D from table Y, where N like 'abc123"),
From A
      Inner Join B on A.A = B.C

Many thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.