June 18, 2003 at 11:39 am
HELP!!
I have the two following select statements which I need to combine together.
The first goes out and gets the total number of questionnaires for a particular entity called WSI.
The second goes out and gets the number of questionnaires for a particular WSI and the total number of defects for the questionnaires.
They are both being done out of the same table.
The issue is that when I try to do a total and then just ones where there is a defect present I can't configure out how to combine it together.
Query 1
SELECT TOP 100 PERCENT
ISNULL(dbo.Defect_Data.UDL4, 'Unknown') AS WSI,
COUNT(dbo.Defect_Data.DataID) AS QCount
FROM dbo.Defect_Data
WHERE (DATETIME >= '2003050100000000') and (DATETIME < '2003060100000000')
GROUP BY dbo.Defect_Data.UDL4 ORDER BY dbo.Defect_Data.UDL4
Query 2
SELECT TOP 100 PERCENT
ISNULL(dbo.Defect_Data.UDL4, 'Unknown') AS WSI,
COUNT(dbo.Defect_Data.DataID) AS QDefectCount,
SUM(dbo.Defect_Data.SUMDEFECTS) AS DefectSum
FROM dbo.Defect_Data
WHERE (DATETIME >= '2003050100000000') and (DATETIME < '2003060100000000')
AND SUMDEFECTS > 0
GROUP BY dbo.Defect_Data.UDL4 ORDER BY dbo.Defect_Data.UDL4
COMBINED QUERY WHICH DOES NOT WORK
SELECT TOP 100 PERCENT
ISNULL(dbo.Defect_Data.UDL4, 'Unknown') AS WSI,
COUNT(dbo.Defect_Data.DataID) AS QDefectCount,
SUM(dbo.Defect_Data.SUMDEFECTS) AS DefectSum,
DefectQTotal = (Select count(dbo.Defect_Data.DataID) FROM dbo.Defect_Data WHERE (DATETIME >= '2003050100000000') and (DATETIME < '2003060100000000') AND SUMDEFECTS > 0),
SumTotal = (Select SUM(dbo.Defect_Data.SUMDEFECTS) FROM dbo.Defect_Data WHERE (DATETIME >= '2003050100000000') and (DATETIME < '2003060100000000') AND SUMDEFECTS > 0)
FROM dbo.Defect_Data
WHERE (DATETIME >= '2003050100000000') and (DATETIME < '2003060100000000')
AND SUMDEFECTS > 0
GROUP BY dbo.Defect_Data.UDL4 ORDER BY dbo.Defect_Data.UDL4
Any help would be greatly appreciated. I am trying to then use this query to display a table of
the summary information.
Edited by - drewid on 06/18/2003 12:24:40 PM
June 18, 2003 at 12:11 pm
In your last query, your are not grouping by the same number of columns you are trying to aggregate, so my guess is you are receiving an error saying "is invalid in the select list because it is not contained in either an aggregate..." and so on. Howerver, you have attempted to include a subselected scalar aggregate (DefectQTotal = (Select count(dbo.Defect_Data.DataID) FROM dbo.Defect_Data WHERE (DATETIME >= '2003050100000000') and (DATETIME < '2003060100000000') AND SUMDEFECTS > 0)) in the SELECT expression, which doesn't make sense, since you are trying to GROUP on a value that doesn't exist in the main table. YOu will have to SELECT these scalar aggregates in a separate statement. And, BTW, why the TOP 100 Percent???:
DECLARE @CountDefectsTotal INT
DECLARE @SumDefectsTotal INT
--
SELECT @CountDefectsTotal = count(dbo.Defect_Data.DataID) FROM dbo.Defect_Data WHERE (DATETIME >= '2003050100000000') and (DATETIME < '2003060100000000') AND SUMDEFECTS > 0
SELECT @SumDefectsTotal = SUM(dbo.Defect_Data.SUMDEFECTS) FROM dbo.Defect_Data WHERE (DATETIME >= '2003050100000000') and (DATETIME < '2003060100000000') AND SUMDEFECTS > 0
--
SELECT
ISNULL(dbo.Defect_Data.UDL4, 'Unknown') AS WSI
, COUNT(dbo.Defect_Data.DataID) AS QDefectCount
, SUM(dbo.Defect_Data.SUMDEFECTS) AS DefectSum
FROM dbo.Defect_Data
WHERE (DATETIME >= '2003050100000000')
AND (DATETIME < '2003060100000000')
AND SUMDEFECTS > 0
GROUP BY dbo.Defect_Data.UDL4
ORDER BY dbo.Defect_Data.UDL4
Then you can do something like this with the scalars:
SELECT
ISNULL(dbo.Defect_Data.UDL4, 'Unknown') AS WSI
, COUNT(dbo.Defect_Data.DataID) AS QDefectCount
, (COUNT(dbo.Defect_Data.DataID) / @CountDefectsTotal) * 100 AS DefectCountPercent
, SUM(dbo.Defect_Data.SUMDEFECTS) AS DefectSum
, (SUM(dbo.Defect_Data.SUMDEFECTS) / @SumDefectsTotal) * 100 AS DefectSumPercent
FROM dbo.Defect_Data
WHERE (DATETIME >= '2003050100000000')
AND (DATETIME < '2003060100000000')
AND SUMDEFECTS > 0
GROUP BY dbo.Defect_Data.UDL4
ORDER BY dbo.Defect_Data.UDL4
HTH,
jay
June 18, 2003 at 12:24 pm
To be more specific, I am trying to obtain the total of questionnaires in the
query per "WSI" as opposed to the percentage from the whole.
In other words:
Number of questionnaires per WSI
Number of questionnaires per WSI where SumDefects > 0
Sum of SumDefects field per WSI
This select statement is being called from an ASP page as opposed to in the sql
stored procedures etc.
June 18, 2003 at 12:33 pm
quote:
To be more specific, I am trying to obtain the total of questionnaires in thequery per "WSI" as opposed to the percentage from the whole.
This select statement is being called from an ASP page as opposed to in the sql
stored procedures etc.
I think it would be better if you used more than one resultset, but:
SELECT WSI, QDefectCount, DefectSum, CountAllDefects, SumAllDefects
FROM
(
SELECT
ISNULL(dbo.Defect_Data.UDL4, 'Unknown') AS WSI
, COUNT(dbo.Defect_Data.DataID) AS QDefectCount
, SUM(dbo.Defect_Data.SUMDEFECTS) AS DefectSum
, (SUM(dbo.Defect_Data.SUMDEFECTS) / @SumDefectsTotal) * 100 AS DefectSumPercent
FROM dbo.Defect_Data
WHERE (DATETIME >= '2003050100000000')
AND (DATETIME < '2003060100000000')
AND SUMDEFECTS > 0
GROUP BY dbo.Defect_Data.UDL4
ORDER BY dbo.Defect_Data.UDL4
)
,
(
SELECT COUNT(dbo.Defect_Data.DataID) AS CountAllDefects
FROM dbo.Defect_Data
WHERE (DATETIME >= '2003050100000000')
and (DATETIME < '2003060100000000')
AND SUMDEFECTS > 0
)
,
(
SELECT SUM(dbo.Defect_Data.SUMDEFECTS) AS SumAllDefects
FROM dbo.Defect_Data
WHERE (DATETIME >= '2003050100000000')
and (DATETIME < '2003060100000000')
AND SUMDEFECTS > 0
)
June 18, 2003 at 2:07 pm
Jay,
I tried your suggestion but am getting an error:
It says "Incorrect Syntax" by the commas separating the last two
select statements.
I also had to add a TOP command because of the order by.
It gave an invalid message without it.
Drew
June 18, 2003 at 2:24 pm
OK, completely different strategy. What about this:
SELECT TOP 100 PERCENT
ISNULL(dbo.Defect_Data.UDL4, 'Unknown') AS WSI
, COUNT(dbo.Defect_Data.DataID) AS QDefectCount
, SUM(dbo.Defect_Data.SUMDEFECTS) AS DefectSum
FROM dbo.Defect_Data
WHERE (DATETIME >= '2003050100000000')
AND (DATETIME < '2003060100000000')
AND SUMDEFECTS > 0
GROUP BY dbo.Defect_Data.UDL4
WITH ROLLUP
The last row of this query will give you your rollup totals. I am really not quite sure about my first attempt...I guess I really didn't understand why you wanted to repeat the same scalar subquery over and over again for every record in the resultset...
June 18, 2003 at 2:36 pm
🙁 The problem is that I am also trying to get the total questionnaires per WSI
not just the one that have SUMDEFECTS > 0.
I need to have it as one so I can then build the html table with the summary
information.
June 18, 2003 at 2:39 pm
With all the time we spent on this thread you probably could have just done 2 calls to the database, and used the results of the second call in your summary row...
I'm all out of ideas. Generally, I try to break unrelated queries into different resultsets; perhaps someone else out there has an answer...
June 19, 2003 at 3:40 am
You need to use a CASE statment instead of WHERE clause to restrict the values going into the aggregate. The code below assumes that you can't have negative values in dbo.Defect_Data.SUMDEFECTS. So you don't need to restrict the input to the SUM. If you did, you would use a CASE statement as with QDefectCount, but replacing '1' with
'dbo.Defect_Data.SUMDEFECTS'.
select
coalesce(dbo.Defect_Data.UDL4, 'Unknown') WSI
,count(dbo.Defect_Data.DataID) QCount
,sum(case when SUMDEFECTS > 0 then 1 else 0 end QDefectCount,
,sum(dbo.Defect_Data.SUMDEFECTS) DefectSum
from dbo.Defect_Data
where ([DateTime] >= '2003050100000000')
and ([DateTime] < '2003060100000000')
group by dbo.Defect_Data.UDL4
order by dbo.Defect_Data.UDL4
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 19, 2003 at 3:40 am
You need to use a CASE statment instead of WHERE clause to restrict the values going into the aggregate. The code below assumes that you can't have negative values in dbo.Defect_Data.SUMDEFECTS. So you don't need to restrict the input to the SUM. If you did, you would use a CASE statement as with QDefectCount, but replacing '1' with
'dbo.Defect_Data.SUMDEFECTS'.
select
coalesce(dbo.Defect_Data.UDL4, 'Unknown') WSI
,count(dbo.Defect_Data.DataID) QCount
,sum(case when SUMDEFECTS > 0 then 1 else 0 end QDefectCount,
,sum(dbo.Defect_Data.SUMDEFECTS) DefectSum
from dbo.Defect_Data
where ([DateTime] >= '2003050100000000')
and ([DateTime] < '2003060100000000')
group by dbo.Defect_Data.UDL4
order by dbo.Defect_Data.UDL4
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 19, 2003 at 3:40 am
You need to use a CASE statment instead of WHERE clause to restrict the values going into the aggregate. The code below assumes that you can't have negative values in dbo.Defect_Data.SUMDEFECTS. So you don't need to restrict the input to the SUM. If you did, you would use a CASE statement as with QDefectCount, but replacing '1' with
'dbo.Defect_Data.SUMDEFECTS'.
select
coalesce(dbo.Defect_Data.UDL4, 'Unknown') WSI
,count(dbo.Defect_Data.DataID) QCount
,sum(case when SUMDEFECTS > 0 then 1 else 0 end QDefectCount,
,sum(dbo.Defect_Data.SUMDEFECTS) DefectSum
from dbo.Defect_Data
where ([DateTime] >= '2003050100000000')
and ([DateTime] < '2003060100000000')
group by dbo.Defect_Data.UDL4
order by dbo.Defect_Data.UDL4
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 19, 2003 at 6:41 am
This is not what the poster is asking for. They are requesting that in the same query, return both scalar and non-scalar aggregates, ie count of all and also count of a single group, and given the format the poster wanted (repeating the scalar aggregate in each row), none of the solutions presented so far fits their requirement.
June 19, 2003 at 7:24 am
SELECT TOP 100 PERCENT
ISNULL(d.UDL4, 'Unknown') AS WSI,
COUNT(d.DataID) AS QCount,
MAX(x.QDefectCount) AS 'QDefectCount',
MAX(x.DefectSum) AS 'DefectSum'
FROM Defect_Data d
INNER JOIN (SELECT ISNULL(UDL4, 'Unknown') AS WSI,
SUM(CASE WHEN SUMDEFECTS > 0 THEN 1 ELSE 0 END) AS QDefectCount,
SUM(CASE WHEN SUMDEFECTS > 0 THEN SUMDEFECTS ELSE 0 END) AS DefectSum
FROM Defect_Data
WHERE ([DATETIME] >= '2003050100000000') and ([DATETIME] < '2003060100000000')
GROUP BY UDL4) x ON x.WSI = ISNULL(d.UDL4, 'Unknown')
WHERE (d.DATETIME >= '2003050100000000') and (d.DATETIME < '2003060100000000')
GROUP BY d.UDL4 ORDER BY d.UDL4
Far away is close at hand in the images of elsewhere.
Anon.
June 19, 2003 at 7:38 am
I don't think so mate - you jumped to a conclusion based on Drewid's FAILED attempt to join the queries together. I think D's utterly pellucid clarification in response should have settled the question; drewid - what do you say?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 19, 2003 at 7:42 am
David,
That last query did it! You rock :).
I was trying to get the total for the WSI as a whole as well as the
total that were defects and that did it. It now makes a very nice
recordset which can be used in the page (all in one trip)!
A big thanks goes to Jay and Tim also for getting the discussion going.
It is great to know that there are smarter people in the world than
myself 😉
Drew
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply