Help Need in Query

Question

Post reply

Help Need in Query

Anjan@Sql

SSCrazy

Points: 2316
More actions
May 24, 2013 at 6:28 am

#273116

Here Is My DDL
Create Table #Tab1
(
Fileid int,
FileName Varchar(10),
FileType varchar(10)
)
go
insert into #Tab1 values(1,'A','txt'),
(1,'A','pdf'),
(2,'B','txt'),
(1,'A','txt'),
(3,'C','txt'),
(3,'C','txt'),
(4,'D','pdf'),
(4,'D','txt'),
(5,'E','txt'),
(6,'F','pdf')
go
--select * from #Tab1
Select Fileid,FileName,FileType,COUNT(*) as [Count]
From #Tab1
Group By Fileid,FileName,FileType
After executing the above query i am getting the count like number of txt files and number of pdf files
group by fileid.
What i want is for any fileid if the txt file was not existed that count should be return as zero in new row?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Cannot understand your question really.

Could you please show exact expected outcome based on sample data provided in your post.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 2

in sample data exept fileid 6 remaining all have the filetype with txt so my output will be like below

Fileid Filename FileType Count

----- ------- -------- -----

1 A pdf1

1 A txt2

2 B txt1

3 C txt2

4 D pdf1

4 D txt1

5 E txt1

6 F txt 0

6 F pdf1

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 3

Try this query... it is suffixing the requirement, but I feel there has to be some other way out to accomplish this.

SELECT D.FileId, D.filetype, COUNT(*)

FROM #Tab1 D

GROUP BY D.FILEID, D.FILETYPE

UNION ALL

SELECT FileId, 'txt', 0

FROM #Tab1 a WHERE NOT EXISTS (SELECT 1 FROM #Tab1 b where filetype = 'txt' and a.fileid = b.fileid)

ORDER BY D.FILEID

sarwaanmca Right there with Babe Points: 796 More actions · Answer 4

After executing the above query i am getting the count like number of txt files and number of pdf files
group by fileid.
What i want is for any fileid if the txt file was not existed that count should be return as zero in new row?

Actually I Can't Understand ur Request but im giving u Sample Solution ....

Select Fileid,FileName,FileType,COUNT(*) as [Count]

into #Temp

From #Tab1

Group By Fileid,FileName,FileType

update #Temp

set Count=0

where FileType='txt'

select * from #Temp

Thanks & Regards

Saravanan.D

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 5

With out using Sub Queries is it possible to get the required output

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 6

Why would you care if the subquery produces the desired result?

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Ananth@Sql (5/24/2013)
With out using Sub Queries is it possible to get the required output

It is absolutely possible by using a left join. The question is why does is matter if you don't use a subselect? The NOT EXISTS would perform better.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

Sean Lange (5/24/2013)
Ananth@Sql (5/24/2013)
With out using Sub Queries is it possible to get the required output
It is absolutely possible by using a left join. The question is why does is matter if you don't use a subselect? The NOT EXISTS would perform better.

Do you mean by LEFT JOIN replacing NOT EXISTS?

If so, it really depends. NOT EXISTS is not always faster (even so, we could expect it would)

But anyway, it doesn't make much difference.

I wonder why OP does worry about the sub query used here?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Eugene Elutin (5/24/2013)
Sean Lange (5/24/2013)
Ananth@Sql (5/24/2013)
With out using Sub Queries is it possible to get the required output
It is absolutely possible by using a left join. The question is why does is matter if you don't use a subselect? The NOT EXISTS would perform better.
Do you mean by LEFT JOIN replacing NOT EXISTS?
If so, it really depends. NOT EXISTS is not always faster (even so, we could expect it would)
But anyway, it doesn't make much difference.
I wonder why OP does worry about the sub query used here?

I did mean that way. You are correct it is not always faster but it would be most of the time. It is more code and I think harder to decipher.

SELECT D.FileId, D.filetype, COUNT(*)

FROM #Tab1 D

GROUP BY D.FILEID, D.FILETYPE

UNION ALL

SELECT a.FileID, 'txt', 0

FROM #Tab1 a

LEFT JOIN #Tab1 b ON a.fileid = b.fileid

AND b.filetype = 'txt'

WHERE b.FileID IS NULL

ORDER BY D.FILEID

The only reason I can speculate about needing to not use subqueries is maybe it was specified on the assignment. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

...
I did mean that way. You are correct it is not always faster but it would be most of the time. It is more code and I think harder to decipher.
...

The best way to see what's going on it's to have a look execution plans of both types of query.

You will find that are almost identical, both will end up doing Left Join but of different type:

"NOT EXISTS" would end up with Left Anti Semi Join and Left Join just simple Left Outer Join. Which one faster will depend on table structure and data.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Eugene Elutin (5/24/2013)
...
I did mean that way. You are correct it is not always faster but it would be most of the time. It is more code and I think harder to decipher.
...
The best way to see what's going on it's to have a look execution plans of both types of query.
You will find that are almost identical, both will end up doing Left Join but of different type:
"NOT EXISTS" would end up with Left Anti Semi Join and Left Join just simple Left Outer Join. Which one faster will depend on table structure and data.

Absolutely. Which brings us full circle. The OP should be asking the question, "Can this be done without subqueries, and which method will be faster?". That would of course lead us to getting actual structure and details about data size. Then we could help them find the best solution.

Since none of that was posted I simply posted a version and said it would likely be slower. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sarwaanmca Right there with Babe Points: 796 More actions · Answer 12

With out using Sub Queries is it possible to get the required output

Try This One...

SELECT D.FileId, D.filetype, COUNT(*)

FROM #Tab1 D

GROUP BY D.FILEID, D.FILETYPE

UNION ALL

SELECT max(FileId), 'txt', 0

FROM #Tab1

Order by D.FileId

Thanks & Regards,

Saravanan.D

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 13

SELECT D.FileId, D.filetype, COUNT(*)
FROM #Tab1 D
GROUP BY D.FILEID, D.FILETYPE
UNION ALL
SELECT max(FileId), 'txt', 0
FROM #Tab1
Order by D.FileId[/code]

As per my understanding this is not what post required, your query will only return max(fileid) in the table (i.e currently it is 6), but in the rows increased it will be max of that, and what required is all fileid without having any filetype with 'txt'.