Merge two SQL query results into one result

Question

I have query like below:

SELECT COUNT(*) AS AppleSupports 
FROM VendorItemPricing 
WHERE VendorName = 'Apple'

SELECT COUNT(*) AS HpSupports 
FROM VendorItemPricing 
WHERE VendorName = 'HP'

Above queries give me results like below:

AppleSupports
63

HpSupports
387

How can make my query to get results in one row like below?

AppleSupports    HpSupports
63               387

Answer 1

Use sub-queries inside your select statement:

SELECT
(select count(*) from VendorItemPricing where VendorName = 'Apple') as AppleSupports,
(select count(*) from VendorItemPricing where VendorName = 'HP') AS HpSupports

Answer 2

Ideally you should do this,

select [Apple] as AppleSupport, [Hp] as HpSupport from (
    select VendorName from VendorItemPricing
) as sourcetable
pivot 
( count(VendorName)
 for VendorName in ([Apple],[Hp])
 )  as pivottable

Further you can add values (like Apple, Hp) for more columns in result set

Answer 3

Select   Sum(Case When vp.VendorName = 'Apple' Then 1 Else 0 End) As AppleSupports
        ,Sum(Case When vp.VendorName = 'HP' Then 1 Else 0 End) As HpSupports
From    VendorItemPricing As vp With (Nolock)
Where   vp.VendorName In ('Apple','HP')

Answer 4

Try this.

SELECT SUM(AppleSupports) AS AppleSupports, SUM(HpSupports) AS HpSupports
FROM 
(
    SELECT CASE WHEN VendorName = 'Apple' 
               THEN COUNT( *) END AS AppleSupports,
          CASE WHEN VendorName = 'HP' 
               THEN COUNT(*) END AS HpSupports
    FROM VendorItemPricing 
    GROUP BY VendorName
) AS A

Answer 5

It'd require a simple join of the query. Try this:

select * from 
(select  count(*) as AppleSupports from VendorItemPricing where VendorName = 'Apple'),
(select  count(*) as HpSupports from VendorItemPricing where VendorName = 'HP')

Answer 6

Simplest way is:

SELECT 
    COUNT(CASE WHEN VendorName = 'Apple' Then 1 End) As AppleSupports,
    COUNT(CASE WHEN VendorName = 'HP'    THEN 1 End) As HpSupports
FROM
    VendorItemPricing