September 30, 2008 at 8:51 pm
Its urgent...
Hi i want make a query such like there is a there is a 3 not null value between column img1 to img10 the query shud give me the count 3 ...
if there is 1 not null value thn 1
eg ...this table
datatype for img col is image
there are 3 not null rows so the count shud be 3 and
there are 4 not null rows so the count shud be 2
col1col1col1img1img2img3img4img5img6img7imp8img9img10
abcabcabcnullvaluevaluevaluenullnullnullnullnullnull
abcabcabcnullnullnullnullnullnullnullvaluevaluevalue
abcabcabcnullnullnullnullnullnullvaluevaluevaluevalue
abcabcabcvaluevaluevaluevaluenullnullnullnullnullnull
abcabcabcnullnullnullnullvaluevaluevaluenullnullnull
September 30, 2008 at 10:00 pm
waiting for the reply
September 30, 2008 at 10:22 pm
It would really be helpful if, in the future, you would pose such questions with a properly formed create table statement and some ready to use data. Please see the link in my signature on how to do that easily. 😉
Here's some code I did for a previous example that does what you want. Details are in the comments.
drop table dbo.ZipPhone
drop table #myhead
go
--===== Create the test table (NOT part of the solution)
CREATE TABLE dbo.ZipPhone
(
ZipCode VARCHAR(6),
Phone1 DECIMAL(10,0),
Phone2 DECIMAL(10,0),
Phone3 DECIMAL(10,0),
Phone4 DECIMAL(10,0),
Phone5 DECIMAL(10,0),
Phone6 DECIMAL(10,0),
Phone7 DECIMAL(10,0),
Phone8 DECIMAL(10,0),
Phone9 DECIMAL(10,0),
Phone10 DECIMAL(10,0)
)
--===== Populate the test table with the data given in the post (NOT part of the solution)
INSERT INTO dbo.ZipPhone
(ZipCode,Phone1,Phone2,Phone3,Phone4,Phone5,Phone6,Phone7,Phone8,Phone9,Phone10)
SELECT 12345,null,1111111111,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999 UNION ALL
SELECT 12346,null,null,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999 UNION ALL
SELECT 12347,null,1111111111,2222222222,3333333333,null,555555555,6666666666,7777777777,8888888888,999999999 UNION ALL
SELECT 12348,null,null,2222222222,null,4444444444,555555555,6666666666,7777777777,8888888888,999999999
--===== SOLUTION STARTS HERE! =====
--===== Create a working table to hold normalized data for future denormalization
CREATE TABLE #MyHead
(ZipCode VARCHAR(6) NOT NULL,
Phone DECIMAL(10) NOT NULL,
OldCol TINYINT NOT NULL,
NewCol TINYINT NOT NULL)
--===== "Normalize" the data using an "old fashioned" method for "unpivoting" data
-- eliminating NULLs in the process AND keeping track of the "old" phone column number.
-- Using ">0" is a speed trick that's just a wee bit faster than IS NOT NULL.
INSERT INTO #MyHead
(ZipCode, Phone, OldCol, NewCol)
SELECT ZipCode, Phone1 ,1 ,0 FROM dbo.ZipPhone WHERE Phone1 > 0 UNION ALL
SELECT ZipCode, Phone2 ,2 ,0 FROM dbo.ZipPhone WHERE Phone2 > 0 UNION ALL
SELECT ZipCode, Phone3 ,3 ,0 FROM dbo.ZipPhone WHERE Phone3 > 0 UNION ALL
SELECT ZipCode, Phone4 ,4 ,0 FROM dbo.ZipPhone WHERE Phone4 > 0 UNION ALL
SELECT ZipCode, Phone5 ,5 ,0 FROM dbo.ZipPhone WHERE Phone5 > 0 UNION ALL
SELECT ZipCode, Phone6 ,6 ,0 FROM dbo.ZipPhone WHERE Phone6 > 0 UNION ALL
SELECT ZipCode, Phone7 ,7 ,0 FROM dbo.ZipPhone WHERE Phone7 > 0 UNION ALL
SELECT ZipCode, Phone8 ,8 ,0 FROM dbo.ZipPhone WHERE Phone8 > 0 UNION ALL
SELECT ZipCode, Phone9 ,9 ,0 FROM dbo.ZipPhone WHERE Phone9 > 0 UNION ALL
SELECT ZipCode, Phone10,10,0 FROM dbo.ZipPhone WHERE Phone10 > 0
--===== Here's the trick... we need this clustered index to support the
-- ranking we're going to do while maintaining the original order
-- of the phone numbers based on the "old" phone column number.
ALTER TABLE #MyHead
ADD PRIMARY KEY CLUSTERED (ZipCode,OldCol)
--===== Ok... all setup to do a "quirky update" to do some ranking...
-- Declare the variables we need to do the actual ranking.
-- The names of the variables pretty much tell all.
DECLARE @PrevZipCode VARCHAR(6)
DECLARE @PrevNewCol TINYINT
SET @PrevZipCode = ''
SET @PrevNewCol = 0
--===== Do the "quirky update" that will do "Ordinal Ranking" of the phone numbers
-- according to their original relative position to each other in the original data.
-- In other words, we're doing this to preserve the order in which the original
-- phone numbers appear in the original data, but vertically instead of horizontally.
-- Also, if you just wanted a "normalized" table, you could stop here.
-- Note that this avoids the hugely expensive "triangular join" method.
UPDATE #MyHead
SET @PrevNewCol = NewCol = CASE WHEN ZipCode = @PrevZipCode THEN @PrevNewCol + 1 ELSE 1 END,
@PrevZipCode = ZipCode
FROM #MyHead WITH(INDEX(0)) --Forces the clustered index to be scanned in order for the update
--Note that this ONLY works on these "quirky updates" and cannot
--be depended on for SELECT's.
--===== This just shows what is meant by "Ordinal Ranking.
-- Look at the "NewCol" to see what I mean.
-- This is just for demonstration and is NOT part of the solution.
SELECT *
FROM #MyHead
ORDER BY ZipCode,NewCol
--===== Ok, we're all set... product a result set that meets the requirement of shifting
-- all the phone numbers to the left, in their original order, so that all NULLs
-- end up on the far right.
-- This is what you call a "CrossTab" or "Pivot" and can be found in Books Online
SELECT ZipCode,
MAX(CASE WHEN NewCol = 1 THEN Phone END) AS Phone1,
MAX(CASE WHEN NewCol = 2 THEN Phone END) AS Phone2,
MAX(CASE WHEN NewCol = 3 THEN Phone END) AS Phone3,
MAX(CASE WHEN NewCol = 4 THEN Phone END) AS Phone4,
MAX(CASE WHEN NewCol = 5 THEN Phone END) AS Phone5,
MAX(CASE WHEN NewCol = 6 THEN Phone END) AS Phone6,
MAX(CASE WHEN NewCol = 7 THEN Phone END) AS Phone7,
MAX(CASE WHEN NewCol = 8 THEN Phone END) AS Phone8,
MAX(CASE WHEN NewCol = 9 THEN Phone END) AS Phone9,
MAX(CASE WHEN NewCol = 10 THEN Phone END) AS Phone10
FROM #MyHead
GROUP BY ZipCode
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2008 at 10:28 pm
jeff thkz for the reply but i dnt want this.....
i want the count
September 30, 2008 at 10:38 pm
Unfortunately, we aren't mind readers. The help you get is only as good as the information you provide with your question.
I suggest you read the article in Jeff's signature line, as it provides excellent instructions on how best to ask for assistance.
The more information you provide, the better we can help you.
😎
September 30, 2008 at 10:58 pm
samsql (9/30/2008)
jeff thkz for the reply but i dnt want this.....i want the count
Yeah... You'e right. I misread your requirements. Sorry.
If you could provide a little test data like I suggest in the article in the link in my signature, we can probably figure something out pretty quick.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2008 at 11:09 pm
samsql (9/30/2008)
Its urgent...Hi i want make a query such like there is a there is a 3 not null value between column img1 to img10 the query shud give me the count 3 ...
if there is 1 not null value thn 1
eg ...this table
datatype for img col is image
there are 3 not null rows so the count shud be 3 and
there are 4 not null rows so the count shud be 2
col1col1col1img1img2img3img4img5img6img7imp8img9img10
abcabcabcnullvaluevaluevaluenullnullnullnullnullnull
abcabcabcnullnullnullnullnullnullnullvaluevaluevalue
abcabcabcnullnullnullnullnullnullvaluevaluevaluevalue
abcabcabcvaluevaluevaluevaluenullnullnullnullnullnull
abcabcabcnullnullnullnullvaluevaluevaluenullnullnull
Sorry, I really wanted to help you, but for few minutes I just read you question over and over again, and I didn’t understand what you want. As I started reading the answers that you got, I realize that I’m not the only one that doesn’t understand what you want. Any reason that you ignore all the requests for reposting your question but this time ask the proper way (post DDL, insert statements and explain what you want)? Doing this would really improve you chances of getting an answer.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 30, 2008 at 11:34 pm
Actaul table contains diffrent datatype for image1 to image10 it is image datatype bcoz it contains images
CREATE TABLE dbo.table2
(
col1 VARCHAR(10),
image1 VARCHAR(10),
image2 VARCHAR(10),
image3 VARCHAR(10),
image4 VARCHAR(10),
image5 VARCHAR(10),
image6 VARCHAR(10),
image7 VARCHAR(10),
image8 VARCHAR(10),
image9 VARCHAR(10),
image10 VARCHAR(10),
col2 VARCHAR(10))
---- the test table with the data given
INSERT INTO dbo.table2
(col1,image1,image2,image3,image4,image5,image6,image7,image8,image9,image10,col2)
SELECT 12345,null,null,null,null,null,null,null,'notnull','notnull','notnull','abc' UNION ALL
SELECT 12346,'notnull',null,'notnull',null,null,null,null,null,null,'notnull','abc' UNION ALL
SELECT 12347,null,null,null,'notnull','notnull',null,null,null,'notnull',null,'abc' UNION ALL
SELECT 12348,null,null,'notnull',null,'notnull','notnull',null,'notnull',null,null,'abc' UNION ALL
SELECT 12348,null,null,'notnull',null,'notnull','notnull',null,null,null,'notnull','abc'
12345NULLNULLNULLNULLNULLNULLNULLnotnullnotnullnotnullabc
12346notnullNULLnotnullNULLNULLNULLNULLNULLNULLnotnullabc
12347NULLNULLNULLnotnullnotnullNULLNULLNULLnotnullNULLabc
12348NULLNULLnotnullNULLnotnullnotnullNULLnotnullNULLNULLabc
12348NULLNULLnotnullNULLnotnullnotnullNULLNULLNULLnotnullabc
i want a query which gives me output like this ....
select count(1) from table2 -- output shud be 3
for 3 rows which contain 3 not null values and
select count(1) from table2 -- output shud be 2
for 2 rows which contain 4 not null values
October 1, 2008 at 12:18 am
jeff waiting for the reply
how many rows are there with 3 not null values and
how many rows are there with 4 not null values
from column image1 to image10
October 1, 2008 at 1:03 am
Try this
Select Cnt as Count1 from (
Select 10 - (Img1 + Img2 + Img3 + Img4 + Img5 + Img6 + Img7 + Img7 + Img8 + Img9 + Img10) as Cnt from (
Select Case IsNull([image1], 1) When 1 Then 1 Else 0 End Img1,
Case IsNull([image2], 1) When 1 Then 1 Else 0 End Img2,
Case IsNull([image3], 1) When 1 Then 1 Else 0 End Img3,
Case IsNull([image4], 1) When 1 Then 1 Else 0 End Img4,
Case IsNull([image5], 1) When 1 Then 1 Else 0 End Img5,
Case IsNull([image6], 1) When 1 Then 1 Else 0 End Img6,
Case IsNull([image7], 1) When 1 Then 1 Else 0 End Img7,
Case IsNull([image8], 1) When 1 Then 1 Else 0 End Img8,
Case IsNull([image9], 1) When 1 Then 1 Else 0 End Img9,
Case IsNull([image10], 1) When 1 Then 1 Else 0 End Img10 from table2) C) P
Group by Cnt
October 1, 2008 at 1:21 am
nope this is not the case...
October 1, 2008 at 4:12 am
any one plz reply ... need the query
October 1, 2008 at 5:51 am
This may help you.
-- *** Test Data **
DECLARE @t TABLE
(
    Col1 int NOT NULL
    ,Image1 varchar(10) NULL
    ,Image2 varchar(10) NULL
    ,Image3 varchar(10) NULL
    ,Image4 varchar(10) NULL
    ,Image5 varchar(10) NULL
    ,Image6 varchar(10) NULL
    ,Image7 varchar(10) NULL
    ,Image8 varchar(10) NULL
    ,Image9 varchar(10) NULL
    ,Image10 varchar(10) NULL
)
INSERT INTO @t
SELECT 12345,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'notnull','notnull','notnull' UNION ALL
SELECT 12346,'notnull',NULL,'notnull',NULL,NULL,NULL,NULL,NULL,NULL,'notnull' UNION ALL
SELECT 12347,NULL,NULL,NULL,'notnull','notnull',NULL,NULL,NULL,'notnull',NULL UNION ALL
SELECT 12348,NULL,NULL,'notnull',NULL,'notnull','notnull',NULL,'notnull',NULL,NULL UNION ALL
-- Assuming Col1 is meant to be PK so made this 12349
SELECT 12349,NULL,NULL,'notnull',NULL,'notnull','notnull',NULL,NULL,NULL,'notnull'
-- *** End Test Data ***
SELECT COUNT(*) AS RowsWith
    ,NotNulls
FROM
(
    SELECT Col1, COUNT(NImage) As NotNulls
    FROM
    (
        SELECT T.Col1
            ,CASE N.N
                WHEN 1 THEN Image1
                WHEN 2 THEN Image2
                WHEN 3 THEN Image3
                WHEN 4 THEN Image4
                WHEN 5 THEN Image5
                WHEN 6 THEN Image6
                WHEN 7 THEN Image7
                WHEN 8 THEN Image8
                WHEN 9 THEN Image9
                WHEN 10 THEN Image10
            END AS NImage
        FROM @t T
            CROSS JOIN
            (
                SELECT 1 UNION ALL
                SELECT 2 UNION ALL
                SELECT 3 UNION ALL
                SELECT 4 UNION ALL
                SELECT 5 UNION ALL
                SELECT 6 UNION ALL
                SELECT 7 UNION ALL
                SELECT 8 UNION ALL
                SELECT 9 UNION ALL
                SELECT 10
            ) N (N) -- or use number/tally table
    ) D
    GROUP BY Col1
) V
GROUP BY NotNulls
October 1, 2008 at 6:11 am
ABOVE QUERY IS GIVING CONVERTION ERROR, WITH SLIGHT CHANGES DONE.
Select Cnt as Count1 from
(
Select 10 - (CAST(Img1 AS INT)+ CAST(Img2 AS INT) + CAST(Img3 AS INT) + CAST(Img4 AS INT) + CAST(Img5 AS INT) +
CAST(Img6 AS INT) + CAST(Img7 AS INT)+ CAST(Img7 AS INT) + CAST(Img8 AS INT)+ CAST(Img9 AS INT) + CAST(Img10 AS INT)) as Cnt from
(
Select Case CAST(IsNull([image1], 1) AS CHAR) When '1' Then '1' Else '0' End Img1,
Case CAST(IsNull([image2], 1) AS CHAR) When '1' Then '1' Else '0' End Img2,
Case CAST(IsNull([image3], 1) AS CHAR) When '1' Then '1' Else '0' End Img3,
Case CAST(IsNull([image4], 1) AS CHAR) When '1' Then '1' Else '0' End Img4,
Case CAST(IsNull([image5], 1) AS CHAR) When '1' Then '1' Else '0' End Img5,
Case CAST(IsNull([image6], 1) AS CHAR) When '1' Then '1' Else '0' End Img6,
Case CAST(IsNull([image7], 1) AS CHAR) When '1' Then '1' Else '0' End Img7,
Case CAST(IsNull([image8], 1) AS CHAR) When '1' Then '1' Else '0' End Img8,
Case CAST(IsNull([image9], 1) AS CHAR) When '1' Then '1' Else '0' End Img9,
Case CAST(IsNull([image10], 1) AS CHAR) When '1' Then '1' Else '0' End Img10 from table2
)C
) P
Group by Cnt ;
October 1, 2008 at 6:18 am
samsql (10/1/2008)
jeff waiting for the replyhow many rows are there with 3 not null values and
how many rows are there with 4 not null values
from column image1 to image10
Actually, most of my original answer was correct for what you need... The Select with all the unions in it that eliminates the nulls is the first step... then you just need to COUNT the data base on the row key.
DECLARE @t TABLE
(
Col1 int NOT NULL
,Image1 varchar(10) NULL
,Image2 varchar(10) NULL
,Image3 varchar(10) NULL
,Image4 varchar(10) NULL
,Image5 varchar(10) NULL
,Image6 varchar(10) NULL
,Image7 varchar(10) NULL
,Image8 varchar(10) NULL
,Image9 varchar(10) NULL
,Image10 varchar(10) NULL
)
INSERT INTO @t
SELECT 12345,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'notnull','notnull','notnull' UNION ALL
SELECT 12346,'notnull',NULL,'notnull',NULL,NULL,NULL,NULL,NULL,NULL,'notnull' UNION ALL
SELECT 12347,NULL,NULL,NULL,'notnull','notnull',NULL,NULL,NULL,'notnull',NULL UNION ALL
SELECT 12348,NULL,NULL,'notnull',NULL,'notnull','notnull',NULL,'notnull',NULL,NULL UNION ALL
-- Assuming Col1 is meant to be PK so made this 12349
SELECT 12349,NULL,NULL,'notnull',NULL,'notnull','notnull',NULL,NULL,NULL,'notnull'
-- *** End Test Data ***
SELECT Col1, SUM(ImagePresent) AS Images
FROM (--=== Derived table "d" counts each non-null image value
SELECT Col1, 1 AS ImagePresent FROM @t WHERE Image1 IS NOT NULL UNION ALL
SELECT Col1, 1 FROM @t WHERE Image2 IS NOT NULL UNION ALL
SELECT Col1, 1 FROM @t WHERE Image3 IS NOT NULL UNION ALL
SELECT Col1, 1 FROM @t WHERE Image4 IS NOT NULL UNION ALL
SELECT Col1, 1 FROM @t WHERE Image5 IS NOT NULL UNION ALL
SELECT Col1, 1 FROM @t WHERE Image6 IS NOT NULL UNION ALL
SELECT Col1, 1 FROM @t WHERE Image7 IS NOT NULL UNION ALL
SELECT Col1, 1 FROM @t WHERE Image8 IS NOT NULL UNION ALL
SELECT Col1, 1 FROM @t WHERE Image9 IS NOT NULL UNION ALL
SELECT Col1, 1 FROM @t WHERE Image10 IS NOT NULL)d
GROUP BY Col1
I imagine you can figure out the rest... think of using the whole code above as yet another derived table to do your counts of counts. 😉
By the way... the table as you have it is in pretty bad shape and totally denormalized. The result of the "derived" table in my code above is what this table should actually look like. If you need an image sequence number, you could certainly add that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply