can we combine these 3 statements into one single query

SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp

SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.idSELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
UNION ALL
SELECT 2 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
UNION ALL
SELECT 3 as id, (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id|||The above query doesn't work for me and infact i want to get the percentage in a single query..
Thanks|||I'm "winging" this one wildly, but could you use:SELECT
CAST(Sum(CASE WHEN name IS NOT NULL
AND name <> '' THEN 1 END) AS FLOAT) / Count(*)
FROM dbo.empThis divides the number of names with value by the total to get the percentage of usable names.

-PatP|||How about

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX|||SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
........................

I could not execute the above query, can we write the query like that?
please correct me if i am wrong?|||Don't use Name <> NULL

No value will ever match that:

MyField > NULL will always be false
MyField < NULL will always be false
MyField <> NULL will always be false
MyField = NULL will always be false
and so on for all applicable operators...

try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.|||try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.

My bad. Use this instead:

NULLIF('', TRIM(Name)) IS NOT NULL|||Just curious, but didn't my suggestion work? What results did it produce?

-PatP|||Replace:
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
By:
WHERE LTRIM(ISNULL(name,'')) <> '' ) AS XXX

yabu.

Can these tables be combined?

I have currently created a design which uses three main tables for storing information related to financial actions. The two tables I wish to combine are described below. There is a third table after the OrderTransactions table which contains information about each step of a transaction.

This means that anytime I have to write a query to get information down at the transaction activity level (very frequently), I will have to always perform two joins. Would it be acceptable in this scenario to combine the Orders and OrderTransactions tables, and place a ParentOrderID field in there? A transaction would either have no parent, or would have to belong to a parent that does not have a parent.

This means that the information in the Orders table will be duplicated for each transaction. The data in the Orders table is more or less static after its initial insert. The data there is never updated, no matter which approach is used.

Either approach will work, I'm just looking to see what some of the people more knowledgeable than me think of the situation.

Orders:
Contains the core order information pertaining to all transactions

CREATE TABLE [Orders] (
[OrderID] [int] NOT NULL ,
[MerchantID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentMethodID] [int] NOT NULL ,
[IsTestOrder] [bit] NOT NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
)

Transactions:
Each order may have one or more transactions. All of the information in the Orders table is pertinent to a given transaction.

CREATE TABLE [OrderTransactions] (
[OrderID] [int] NOT NULL ,
[TransactionID] [int] NOT NULL ,
[TransactionTypeID] [int] NOT NULL ,
[CustomerIPAddress] [bigint] NOT NULL ,
[Description] [nvarchar] (250) NOT NULL ,
CONSTRAINT [PK_OrderTransactions] PRIMARY KEY CLUSTERED
(
[OrderID],
[SequenceID]
) ON [PRIMARY] ,
CONSTRAINT [FK_OrderTransactions_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [Orders] (
[OrderID]
)
)I like recursive relationships. Just about every database I build has some element of recursion. But I wouldn't recommend it in this case.

Orders and order transactions are two different types of data. You want to combine them so you can avoid a join under some circumstances. But to check whether a given records represents a transaction you are going to need to use a join anyway, albeit a self-join ("A transaction would either have no parent, or would have to belong to a parent that does not have a parent"). You may save a bit on cacheing, but I doubt it.

Now, if a transaction could, under some business circumstances, represent a transaction, then you would have a good case for recursion. Or if an order could consist of a bundle of smaller orders. And I mean in your business model, not just that you COULD represent it this way in your schema.