CODEWITHSUNDEEP
sqlsql-serversql-server-2008selectalias
K C
K C

Reputation: 219

Providing a column name for the resultant table

I have the below query to extract data in desired format

select
(
    select COUNT(serialNumber) as LOTQty
    from MobileData
    where mobileName = @mobileName and model = @model and LOTQty = @lotQty
)
,(
    select COUNT(serialNumber) as FailedQty
    from MobileData
    where mobileName = @mobileName and model = @model and LOTQty = @lotQty and PreScan = 'FAIL'
)

But when I execute the query it certainly gives the desired result but instead of column names "LotQty" and "FailedQty" it display (No Column Name) and (No Column Name)

I also tried modifying the above query as below but still the result remains same

select
(
    select [LotQty] = COUNT(serialNumber)
    ...
)
,(
    select [FailedQty] = COUNT(serialNumber) 
    ...
)

Any help is appreciated. I'm using sql server 2008

Upvotes: 2

Views: 429

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

You can give colmn name using ALIAS and also you can fetch both count using one query instead of two subqueries.

Try this: 

SELECT COUNT(serialNumber) AS LOTQty, 
       SUM(CASE WHEN PreScan = 'FAIL' THEN 1 ELSE 0 END) AS FailedQty
FROM MobileData
WHERE mobileName = @mobileName AND model = @model AND LOTQty = @lotQty;

Upvotes: 3

juergen d
juergen d

Reputation: 204766

The alias name must be outside the subquery

select
(
    select COUNT(serialNumber) 
    from MobileData
    where mobileName = @mobileName and model = @model and LOTQty = @lotQty
) as LOTQty
,(
    select COUNT(serialNumber)
    from MobileData
    where mobileName = @mobileName and model = @model and LOTQty = @lotQty and PreScan = 'FAIL'
)  as FailedQty

Upvotes: 2

Related Questions