• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

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
0
visionetv
Asked:
visionetv
1 Solution
 
visionetvAuthor Commented:
Correction to the above Post, the last query is for the table [Batch]
0
 
Brian CroweCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
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
 
PortletPaulCommented:
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now