January 22, 2008 at 12:23 pm
I know my limitations and writing mind-numbing queries is one of them so any help is greatly appreciated! I need to be able to display the # of downloads for each particular file. In the following query I need to get the count of "cdds_DataDownload.ZipFileName" for use in a grid but I don't see how that is readily apparent.
SELECT DISTINCT
cdds_Metadata.DatasetId AS ID, cdds_Metadata.DataCurrencyDate AS CurrencyDate, cdds_Metadata.DataFormatType AS DataFormat,
cdds_DataUpload.ZipFileName, cdds_Agencies.WorkUnit + ' - ' + cdds_Agencies.WorkUnitDesc AS AgencyName
FROM cdds_Metadata INNER JOIN
cdds_DataDownload INNER JOIN
cdds_DataUpload INNER JOIN
cdds_Agencies ON cdds_DataUpload.AgencyUniqueId = cdds_Agencies.AgencyUniqueId ON
cdds_DataUpload.ZipFileName = cdds_DataDownload.ZipFileName ON cdds_Metadata.DatasetId = cdds_DataUpload.DatasetId
ORDER BY cdds_DataUpload.ZipFileName
January 22, 2008 at 2:05 pm
How about something like the following:
SELECT (SELECT COUNT(*) FROM Customers) AS totalCount, * FROM Customers
😉
January 22, 2008 at 3:59 pm
CREATE PROC dbo.MyProc
@param1 ...,
@Rcnt int output
AS
SELECT .....
SET @Rcnt = @@ROWCOUNT
GO
Read parameter @Rcnt after SP execution and get your query rows counted. 🙂
_____________
Code for TallyGenerator
January 23, 2008 at 6:40 am
That's exactly what I have in a separate procedure see below. Is there a way to incorporate the count into the existing procedure?
ALTER PROCEDURE dbo.cdds_Download_Count_Select
(
@DataId uniqueidentifier
)
AS
SET NOCOUNT OFF;
DECLARE @RowCount int
SELECT cdds_DataUpload.ZipFileName as Name
FROM cdds_DataUpload
INNER JOIN
cdds_DataDownload ON cdds_DataUpload.DatasetId = cdds_DataDownload.DatasetId
where cdds_DataUpload.DatasetId = @DataId
SET @RowCount = @@ROWCOUNT
RETURN @RowCount
January 23, 2008 at 6:57 am
Hello Brett,
Instead of writing a stored procedure why don't you convert the same to a function and return the count of downloads. So in this way you can get the value into a local variable and then publish the actual result set by using this local variable in the final select statement.
Hope this helps.
Thanks
Lucky
January 23, 2008 at 6:58 am
Don't use RETURN for returning values.
It returns error code and it's better to leave it this way.
ALTER PROCEDURE dbo.cdds_Download_Count_Select
@DataId uniqueidentifier,
@RowCount int OUTPUT
AS
SET NOCOUNT ON
SELECT cdds_DataUpload.ZipFileName as Name
FROM cdds_DataUpload
INNER JOIN
cdds_DataDownload ON cdds_DataUpload.DatasetId = cdds_DataDownload.DatasetId
where cdds_DataUpload.DatasetId = @DataId
SET @RowCount = @@ROWCOUNT
_____________
Code for TallyGenerator
January 23, 2008 at 7:01 am
lucky (1/23/2008)
Hello Brett,Instead of writing a stored procedure why don't you convert the same to a function and return the count of downloads.
Because it will do 2 queries against the same table instead of one.
_____________
Code for TallyGenerator
January 23, 2008 at 7:15 am
SELECT DISTINCT cdds_Metadata.DatasetId AS ID,
cdds_Metadata.DataCurrencyDate AS CurrencyDate,
cdds_Metadata.DataFormatType AS DataFormat,
cdds_DataUpload.ZipFileName, cdds_Agencies.WorkUnit + ' - ' + cdds_Agencies.WorkUnitDesc AS AgencyName,
-- My part here
subqty.QtyDownload
FROM cdds_Metadata
INNER JOIN cdds_DataUpload
ON cdds_Metadata.DatasetId = cdds_DataUpload.DatasetId
INNER JOIN cdds_DataDownload
ON cdds_DataUpload.ZipFileName = cdds_DataDownload.ZipFileName
INNER JOIN cdds_Agencies
ON cdds_DataUpload.AgencyUniqueId = cdds_Agencies.AgencyUniqueId
-- My part here
inner join
(select zipfilename, count(*) as QtyDownload
from cdds_DataDownload
group by zipfilename) SubQty
on cdds_DataDownload.zipfilename = subqty.zipfilename
ORDER BY cdds_DataUpload.ZipFileName
That should give you what you seem to be asking for. You'll want to change the style on the part I typed in (I left it mostly lowercase to make it easy to tell which part I typed).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2008 at 7:53 am
This should do the trick, first GROUP BY with all the output elements will give you the same net effect as DISTINCT, so drop DISTINCT and replace with the appropriate GROUP BY clause. Then you simply add the count for the column you want. (Note: I aliased the tables to make the query a bit more compact to me)
SELECT
MD.DatasetId AS ID,
MD.DataCurrencyDate AS CurrencyDate,
MD.DataFormatType AS DataFormat,
DU.ZipFileName,
A.WorkUnit + ' - ' + A.WorkUnitDesc AS AgencyName,
COUNT(DD.ZipFileName) AS DownloadCount
FROM
dbo.cdds_Metadata MD
INNER JOIN
dbo.cdds_DataUpload DU
INNER JOIN
dbo.cdds_DataDownload DD
INNER JOIN
dbo.cdds_Agencies A
ON
DU.AgencyUniqueId = A.AgencyUniqueId
ON
DU.ZipFileName = DD.ZipFileName
ON
MD.DatasetId = DU.DatasetId
GROUP BY
MD.DatasetId,
MD.DataCurrencyDate,
MD.DataFormatType,
DU.ZipFileName,
A.WorkUnit + ' - ' + A.WorkUnitDesc
ORDER BY
DU.ZipFileName
January 23, 2008 at 8:00 am
SSCrazy Eights,
When I try and run your query in Analyzer I get this:
"The column prefix 'DU' does not match with a table name or alias name used in the query."
As you can tell, I'm not a SQL guy so your help is appreciated.
January 23, 2008 at 8:27 am
Sorry a placement issue, I didn't see Agencies was joined to DataUpload and placed it under DataDownload which caused this error.
SELECT
MD.DatasetId AS ID,
MD.DataCurrencyDate AS CurrencyDate,
MD.DataFormatType AS DataFormat,
DU.ZipFileName,
A.WorkUnit + ' - ' + A.WorkUnitDesc AS AgencyName,
COUNT(DD.ZipFileName) AS DownloadCount
FROM
dbo.cdds_Metadata MD
INNER JOIN
dbo.cdds_DataUpload DU
INNER JOIN
dbo.cdds_Agencies A
ON
DU.AgencyUniqueId = A.AgencyUniqueId
INNER JOIN
dbo.cdds_DataDownload DD
ON
DU.ZipFileName = DD.ZipFileName
ON
MD.DatasetId = DU.DatasetId
GROUP BY
MD.DatasetId,
MD.DataCurrencyDate,
MD.DataFormatType,
DU.ZipFileName,
A.WorkUnit + ' - ' + A.WorkUnitDesc
ORDER BY
DU.ZipFileName
January 23, 2008 at 8:51 am
Hey thanks a lot. I needed a quick turnaround on this and thankfully it works. Peace out.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply