Combining SQL Queries for display on a single DBGrid

Hi Experts;

If possible I would like to combine the below queries into one for purposes of displaying the results in a single DBGrid without using Union or Union All. The 'Master' query would be contained in a Delphi String Editor as part of the Delphi ADO query component.

Five of the query structures are the same with the exception of the variable defined  for the 'Description' column. ADO query strings for table [TenderEntry]...

SELECT
SUM(Amount) AS Checks
FROM [TenderEntry]
WHERE BatchNumber = 1
AND Description = 'Check'

SELECT
SUM(Amount) AS Cash
FROM [TenderEntry]
WHERE BatchNumber = 1
AND Description = 'Cash'

SELECT
SUM(Amount) AS Coupons
FROM [TenderEntry]
WHERE BatchNumber = 1
AND Description = 'Coupons'

SELECT
SUM(Amount) AS GiftCert
FROM [TenderEntry]
WHERE BatchNumber = 1
AND Description = 'GiftCert

SELECT
SUM(Amount) AS CusCredit
FROM [TenderEntry]
WHERE BatchNumber = 1
AND Description = 'CusCredit'

ADO query strings for table [TenderEntry]...

SELECT
CAST(ClosingTime AS date)  AS Date
     , RegisterID                        AS Num
     , BatchNumber                 AS Batch
     , OpeningTotal                  AS Opening
     , ClosingTotal                    AS Closing
FROM  [Batch]

IDE:        Delphi 7
Server:  2008/R2
Type:     ADO

Thank you for help,
Visionetv
visionetvAsked:
Who is Participating?
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.

visionetvAuthor Commented:
Correction to the above Post, the last query is for the table [Batch]
0
Brian CroweDatabase AdministratorCommented:
You didn't specify what you actually wanted for your output so I'm just going to take a quick stab...

SELECT SUM(CASE WHEN [Description] = 'Check' THEN Amount ELSE 0 END) AS Checks,
	SUM(CASE WHEN [Description] = 'Cash' THEN Amount ELSE 0 END) AS Cash,
	SUM(CASE WHEN [Description] = 'Coupons' THEN Amount ELSE 0 END) AS Coupons,
	SUM(CASE WHEN [Description] = 'GiftCert' THEN Amount ELSE 0 END) AS GiftCert,
	SUM(CASE WHEN [Description] = 'CusCredit' THEN Amount ELSE 0 END) AS CusCredit
FROM TenderEntry
WHERE BatchNumber = 1

Open in new window

0
Russell FoxDatabase DeveloperCommented:
You can do them as sub-selects, giving each as a column rather than a row:
SELECT
	(SELECT 
	SUM(Amount) AS Checks 
	FROM [TenderEntry]
	WHERE BatchNumber = 1
	AND Description = 'Check') AS SumChecks,

	(SELECT 
	SUM(Amount) AS Cash
	FROM [TenderEntry]
	WHERE BatchNumber = 1
	AND Description = 'Cash') AS SumCash,

	(SELECT 
	SUM(Amount) AS Coupons
	FROM [TenderEntry]
	WHERE BatchNumber = 1
	AND Description = 'Coupons') AS SumCoupons,

	(SELECT 
	SUM(Amount) AS GiftCert
	FROM [TenderEntry]
	WHERE BatchNumber = 1
	AND Description = 'GiftCert') AS SumGiftCerts,

	(SELECT 
	SUM(Amount) AS CusCredit
	FROM [TenderEntry]
	WHERE BatchNumber = 1
	AND Description = 'CusCredit') AS SumCusCredit,

	CAST(ClosingTime AS date)  AS ClosingDate
     , RegisterID	AS Num
     , BatchNumber	AS Batch
     , OpeningTotal	AS Opening
     , ClosingTotal	AS Closing
FROM  [Batch]

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
You should definitely seek to simplify where possible, and I see no reason not to use Brian Crowe's suggestion. This is much more efficient than hitting the same table multiple times with the same where clause.

But I got lost at "ADO query strings for table [TenderEntry]..."

How does this final query result in one row? It could be 100,000 rows for all we know.
SELECT
CAST(ClosingTime AS date)  AS Date
     , RegisterID                        AS Num
     , BatchNumber                 AS Batch
     , OpeningTotal                  AS Opening
     , ClosingTotal                    AS Closing
FROM  [Batch]

Open in new window


ONLY if that last query is restricted to one row should you consider "merging" them, and IF it is one row, then you can use CROSS JOIN e.g.

select ... 
FROM TenderEntry
WHERE BatchNumber = 1 -- as per Brian's suggestion

CROSS JOIN (
    SELECT TOP 1 --<< NB!
    CAST(ClosingTime AS date)  AS Date
         , RegisterID                        AS Num
         , BatchNumber                 AS Batch
         , OpeningTotal                  AS Opening
         , ClosingTotal                    AS Closing
    FROM  [Batch]
    where some=thing --<< NB!
) x

Open in new window

0
visionetvAuthor Commented:
Sorry, I apparently cut-off a portion of my original post; the last query should have had  WHERE BatchNumber=1

A combination of Brian's and Pauls' Query works outside the Delphi
compiler but not in the IDE or the compiled executable. The compiler complains about 'incorrect syntax near the keyword 'FROM' which I'm researching as a Delphi issue.

Here is the combined Brian/Paul Query...

SELECT SUM(CASE WHEN [Description] = 'Check' THEN Amount ELSE 0 END) AS Checks,
      SUM(CASE WHEN [Description] = 'Cash' THEN Amount ELSE 0 END) AS Cash,
      SUM(CASE WHEN [Description] = 'Coupons' THEN Amount ELSE 0 END) AS Coupons,
      SUM(CASE WHEN [Description] = 'GiftCert' THEN Amount ELSE 0 END) AS GiftCert,
      SUM(CASE WHEN [Description] = 'CusCredit' THEN Amount ELSE 0 END) AS CusCredit
FROM TenderEntry WHERE BatchNumber = 1

SELECT
    CAST(ClosingTime AS date)  AS Date
         , RegisterID          AS Num
         , BatchNumber         AS Batch
         , OpeningTotal        AS Opening
         , ClosingTotal        AS Closing
    FROM  [Batch] WHERE BatchNumber = 1

Russell's query works both inside and outside Delphi though efficiency is an issue as the DB grows.
0
PortletPaulfreelancerCommented:
It can be quite amazing what a small fact can reveal :)

Perhaps this will solve it?
SELECT
      CAST(b.ClosingTime AS date) AS Date
    , b.RegisterID                AS Num
    , b.BatchNumber               AS Batch
    , b.OpeningTotal                AS Opening
    , b.ClosingTotal
    , SUM(CASE
            WHEN [te.Description] = 'Check' THEN te.Amount
            ELSE 0 END)           AS Checks
    , SUM(CASE
            WHEN [te.Description] = 'Cash' THEN te.Amount
            ELSE 0 END)           AS Cash
    , SUM(CASE
            WHEN [te.Description] = 'Coupons' THEN te.Amount
            ELSE 0 END)           AS Coupons
    , SUM(CASE
            WHEN [te.Description] = 'GiftCert' THEN te.Amount
            ELSE 0 END)           AS GiftCert
    , SUM(CASE
            WHEN [te.Description] = 'CusCredit' THEN te.Amount
            ELSE 0 END)           AS CusCredit
FROM TenderEntry te
      INNER JOIN [batch] b
                  ON te.BatchNumber = b.BatchNumber
WHERE te.BatchNumber = 1
GROUP BY
      CAST(b.ClosingTime AS date)
    , b.RegisterID
    , b.BatchNumber
    , b.OpeningTotal
    , b.ClosingTotal
;

Open in new window

0

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
visionetvAuthor Commented:
Works with slight modification;

SELECT
      CAST([Batch].ClosingTime AS date) AS Date
    , [Batch].RegisterID                AS Num
    , [Batch].BatchNumber               AS Batch
    , [Batch].OpeningTotal              AS Opening
    , [Batch].ClosingTotal
    , SUM(CASE
            WHEN [Description] = 'Check' THEN TenderEntry.Amount
            ELSE 0 END)           AS Checks
    , SUM(CASE
            WHEN [Description] = 'Cash' THEN TenderEntry.Amount
            ELSE 0 END)           AS Cash
    , SUM(CASE
            WHEN [Description] = 'Coupons' THEN TenderEntry.Amount
            ELSE 0 END)           AS Coupons
    , SUM(CASE
            WHEN [Description] = 'GiftCert' THEN TenderEntry.Amount
            ELSE 0 END)           AS GiftCert
    , SUM(CASE
            WHEN [Description] = 'CusCredit' THEN TenderEntry.Amount
            ELSE 0 END)           AS CusCredit
FROM TenderEntry
      INNER JOIN [batch]
                  ON TenderEntry.BatchNumber = [Batch].BatchNumber
WHERE TenderEntry.BatchNumber = 1
GROUP BY
      CAST([Batch].ClosingTime AS date)
    , [Batch].RegisterID
    , [Batch].BatchNumber
    , [Batch].OpeningTotal
    , [Batch].ClosingTotal;

Open in new window

Thank you again for your efforts,
Visionetv
0
PortletPaulfreelancerCommented:
no problem.

btw: One of the reasons for using a tables alias b was to avoid the necessity of brackets [batch], but also, you should really prefix the TenderEntry fields too.
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.

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.