Query Combining

This should be easy for someone...
select count(*) from vw_priceTrends where closeprice < 20 and
pricebarrier = 1
select count(*) from vw_priceTrends where closeprice < 20
How do I combine the two queries as one query?Without DDL, just guessing. Does this work?
SELECT
totalCount = COUNT(*),
pbarrierCount = SUM(CASE WHEN pricebarrier=1 THEN 1 ELSE 0 END)
FROM vw_PriceTrends
WHERE closeprice < 20
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Won Lee" <noemail@.notmail.com> wrote in message
news:u3B9$$AUFHA.2472@.TK2MSFTNGP10.phx.gbl...
> This should be easy for someone...
>
> select count(*) from vw_priceTrends where closeprice < 20 and pricebarrier
> = 1
> select count(*) from vw_priceTrends where closeprice < 20
>
> --
> How do I combine the two queries as one query?|||Use a CASE like:
SELECT SUM ( CASE WHEN pricebarrier = 1 THEN 1 ELSE 0 END ),
COUNT ( * )
FROM vw_priceTrends
WHERE closeprice < 20 ;
Anith|||DO you want to add the values, or out the queries togehter in a result set ?
result set:

> select count(*) from vw_priceTrends where closeprice < 20 and pricebarrier
> = 1
UNION
> select count(*) from vw_priceTrends where closeprice < 20
Add
SELECT SUb1.Cnt1+ Sub2Cnt2 From
(
> select count(*) Cnt1 from vw_priceTrends where closeprice < 20 and
> pricebarrier = 1) SUb1,
(
> select count(*) from vw_priceTrends where closeprice < 20
) SUb2
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Won Lee" <noemail@.notmail.com> schrieb im Newsbeitrag
news:u3B9$$AUFHA.2472@.TK2MSFTNGP10.phx.gbl...
> This should be easy for someone...
>
> select count(*) from vw_priceTrends where closeprice < 20 and pricebarrier
> = 1
> select count(*) from vw_priceTrends where closeprice < 20
>
> --
> How do I combine the two queries as one query?|||Sorry didnt see that these are the same tables so that should go for
Select count(*) AS COUNT1,
FROM
SUM((CASE pricebarrier WHEN 1 THEN 1 ELSE 0 END)) Cnt2
vw_priceTrends where closeprice < 20
"Won Lee" <noemail@.notmail.com> schrieb im Newsbeitrag
news:u3B9$$AUFHA.2472@.TK2MSFTNGP10.phx.gbl...
> This should be easy for someone...
>
> select count(*) from vw_priceTrends where closeprice < 20 and pricebarrier
> = 1
> select count(*) from vw_priceTrends where closeprice < 20
>
> --
> How do I combine the two queries as one query?|||Thanks to everyone. Will give it a try.|||This is How I'd do it:
SELECT
(
select count(*) from vw_priceTrends where closeprice < 20 and
pricebarrier = 1
) AS PriceBarrierTrue,
(
select count(*) from vw_priceTrends where closeprice < 20
) AS PriceBarrierFalse
Greg Jackson
PDX, Oregon

Query Assistance Combining Columns into one

I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column.

My Query:

Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID
ORDER BY Product_ID DESC

ISNULL(small_text_value,large_text_value) AS TheValue|||

This would return large_Text when small_text is null. but notice if small_text is null you will get 2 columns with same value. If that is not what you want please post back with more details.

SELECTEAV.PRODUCT_ID,CASEWHEN EAV.SMALL_TEXT_VALUEISNULLTHEN EAV.LARGE_TEXT_VALUEELSE EAV.SMALL_TEXT_VALUEEND , EAV.LARGE_TEXT_VALUEFROMEXTENDED_ATTRIBUTE_VALUES EAVINNERJOIN EXTENDED_ATTRIBUTES EAWHERE EAV.Ext_Att_ID = EA.Ext_Att_IDORDER BY Product_IDDESC

|||

Or

COALESCE(small_text_value,large_text_value) AS TheValue

If the small is null, the data will be from the large column.

|||

Everyones solutions worked perfect. Thanks!

I've now got something else I need to do. To build this info, I need to pull from a few different tables.

I now have the query that we just worked on, and I set it equal to the column of data I need:

Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, ISNULL(small_text_value,large_text_value) AS TheValue, EXTENDED_ATTRIBUTES.Column_Name
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID And
EXTENDED_ATTRIBUTES.Column_Name = '4 Ball EP'
ORDER BY Product_ID DESC

And I now also need the rows from this query:

Select PRODUCT_FEATURE_VALUES.PRODUCT_ID, SHARED_FEATURE_VALUES.Feature_Text_Value As TheValue
From PRODUCT_FEATURE_VALUES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES
Where PRODUCT_FEATURE_VALUES.Feature_Type_ID = SHARED_FEATURE_TYPES.Feature_Type_ID And
SHARED_FEATURE_TYPES.Feature_Type = '4 Ball EP' And
PRODUCT_FEATURE_VALUES.Feature_Value_ID = SHARED_FEATURE_VALUES.Feature_Value_ID

I'd like them to both return in one query if thats possible...

|||If the columns are the same I think you could use a Union statement.|||

I used a Union and that works. I now have:

Select
PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID,
SHARED_FEATURE_VALUES.Feature_Text_Value As TheValue,
SHARED_FEATURE_TYPES.Feature_Type AS ColumnName
From PRODUCT_FEATURE_VALUES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES
Where
PRODUCT_FEATURE_VALUES.Feature_Type_ID = SHARED_FEATURE_TYPES.Feature_Type_ID And
PRODUCT_FEATURE_VALUES.Feature_Value_ID = SHARED_FEATURE_VALUES.Feature_Value_ID
UNION

Select
EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID,
ISNULL(small_text_value,large_text_value) AS TheValue,
EXTENDED_ATTRIBUTES.Column_Name AS ColumnName
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID
ORDER BY Product_ID DESC

How can I display the values in a column as a column. In the 2 fields above that I'm declaring as ColumnName, there are alot of individual values. How can I make one of those values the columnname, and if I want another and so on... I'm trying to build an app for some internal querying and if the user says I want to see all values for A and B, then I would want to display A and B as individual columns, even though they are just a value in the same column of data in the database.

|||

SELECT t1.ProductID,MAX(CASE WHEN Column_Name='A' THEN TheValue ELSE NULL END) AS A, MAX(CASE WHEN ColumnName='B' THEN TheValue ELSE NULL END) AS B

FROM ({Your giant query here}) AS t1

GROUP BY t1.ProductID

or for SQL 2005, you can use the new PIVOT stuff.

SELECT ProductID,A,B

FROM
({Your giant query here}) t1
PIVOT
(
MAX(TheValue)
FOR ColumnName IN
('A','B')
) AS pvt
ORDER BY ProductID

|||

I keep getting Invalid column name 'Column_Name'.

Do I need to set the column name of A and B to the clumns I want displayed?

SELECT t1.ProductID,MAX(CASE WHEN Column_Name='A' THEN TheValue ELSE NULL END) AS A, MAX(CASE WHEN ColumnName='B' THEN TheValue ELSE NULL END) AS B

FROM (Select
PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID,
SHARED_FEATURE_VALUES.Feature_Text_Value As TheValue,
SHARED_FEATURE_TYPES.Feature_Type AS ColumnName
From PRODUCT_FEATURE_VALUES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES
Where
PRODUCT_FEATURE_VALUES.Feature_Type_ID = SHARED_FEATURE_TYPES.Feature_Type_ID And
PRODUCT_FEATURE_VALUES.Feature_Value_ID = SHARED_FEATURE_VALUES.Feature_Value_ID
UNION

Select
EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID,
ISNULL(small_text_value,large_text_value) AS TheValue,
EXTENDED_ATTRIBUTES.Column_Name AS ColumnName
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID) AS t1

GROUP BY t1.ProductID

|||

I got this now and it works good for getting the columns I need. Can I add conditioning for each column after the ColumnName='Value I want as a Column'? Like if I want Test2 As a column, but also only show there Test2 <> 5, where do I add that?

SELECT
t1.ProductID,
MAX(CASE WHEN ColumnName='test1' THEN TheValue ELSE NULL END) AS A,
MAX(CASE WHEN ColumnName='test2' THEN TheValue ELSE NULL END) AS B

FROM (Select
PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID,
SHARED_FEATURE_VALUES.Feature_Text_Value As TheValue,
SHARED_FEATURE_TYPES.Feature_Type AS ColumnName
From PRODUCT_FEATURE_VALUES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES
Where
PRODUCT_FEATURE_VALUES.Feature_Type_ID = SHARED_FEATURE_TYPES.Feature_Type_ID And
PRODUCT_FEATURE_VALUES.Feature_Value_ID = SHARED_FEATURE_VALUES.Feature_Value_ID
UNION

Select
EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID,
ISNULL(small_text_value,large_text_value) AS TheValue,
EXTENDED_ATTRIBUTES.Column_Name AS ColumnName
From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES
Where EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_ID) AS t1

GROUP BY t1.ProductID

|||

I need some more assistance with this since some requirements have changed. Sometimes there will be multiple records with the same columnname, but a distinct value. No matter what I do, its only returning 1 record for each column. I'd like to return all records for each column name and combine them then into one. I dont know if this is possible... In the case base, Military Specification Number is actually in the table 3 times, but this query only grabs the last record of the 3...

SELECT

TOP(100)PERCENT PRODUCT_NUMBER, PRODUCT_NAME,MAX(CASEWHEN ColumnName='Military Specification Number'THEN TheValueELSENULLEND)AS [Military Specification Number]

FROM

(SELECT dbo.PRODUCT_FEATURE_VALUES.PRODUCT_IDAS ProductID, dbo.SHARED_FEATURE_VALUES.FEATURE_TEXT_VALUEAS TheValue,

dbo

.SHARED_FEATURE_TYPES.FEATURE_TYPEAS ColumnName, dbo.PRODUCTS.PRODUCT_NUMBER,

dbo

.PRODUCTS.PRODUCT_NAMEFROM dbo.PRODUCT_FEATURE_VALUESINNERJOIN

dbo

.SHARED_FEATURE_TYPESON

dbo

.PRODUCT_FEATURE_VALUES.FEATURE_TYPE_ID= dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE_IDINNERJOIN

dbo

.SHARED_FEATURE_VALUESON

dbo

.PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID= dbo.SHARED_FEATURE_VALUES.FEATURE_VALUE_IDINNERJOIN

dbo

.PRODUCTSON dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID= dbo.PRODUCTS.PRODUCT_IDUNIONALLSELECT dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_IDAS ProductID,ISNULL(dbo.EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE,

dbo

.EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE)AS TheValue, dbo.EXTENDED_ATTRIBUTES.COLUMN_NAMEAS ColumnName,

PRODUCTS_1

.PRODUCT_NUMBER, PRODUCTS_1.PRODUCT_NAMEFROM dbo.EXTENDED_ATTRIBUTE_VALUESINNERJOIN

dbo

.EXTENDED_ATTRIBUTESON

dbo

.EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID= dbo.EXTENDED_ATTRIBUTES.EXT_ATT_IDINNERJOIN

dbo

.PRODUCTSAS PRODUCTS_1ON dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID= PRODUCTS_1.PRODUCT_ID)AS t1

WHERE

PRODUCT_NUMBER='05048'

GROUP

BY PRODUCT_NUMBER, PRODUCT_NAME

ORDER

BY PRODUCT_NUMBER