I'm curious about why these results are different
I started off with a rough draft query of:
SELECT S.Type,
COUNT(DISTINCT C.StateProvince)
FROM INV_LINE_ITEM ILI JOIN STOVE S ON ILI.FK_StoveNbr = S.SerialNumber
JOIN INVOICE I ON I.InvoiceNbr = ILI.FK_InvoiceNbr
JOIN CUSTOMER C ON I.FK_CustomerID = C.CustomerID
WHERE C.Country = 'USA'
AND S.Type IN (SELECT S.Type
FROM INV_LINE_ITEM ILI JOIN STOVE S ON ILI.FK_StoveNbr = S.SerialNumber
JOIN INVOICE I ON I.InvoiceNbr = ILI.FK_InvoiceNbr
JOIN CUSTOMER C ON I.FK_CustomerID = C.CustomerID
WHERE C.Country = 'CAN' )
GROUP BY S.Type;
And it returned the following results:
Type
--------------- -----------
FiredAlways 9
FiredNow 8
So, once I got the query working the way I wanted to I decided to开发者_StackOverflow中文版 format it some and I noticed something weird.
This query will produce the above results:
SELECT
CAST(S.Type as CHAR (10)) AS 'Stove Type',
COUNT(DISTINCT C.StateProvince) AS 'Number of Sales'
FROM STOVE S, INV_LINE_ITEM ILI, INVOICE I, CUSTOMER C
WHERE S.SerialNumber = ILI.FK_StoveNbr
AND ILI.FK_InvoiceNbr = I.InvoiceNbr
AND I.FK_CustomerID = C.CustomerID
AND C.Country = 'USA'
AND S.Type IN
(SELECT S.Type
FROM STOVE S, INV_LINE_ITEM ILI, INVOICE I, CUSTOMER C
WHERE S.SerialNumber = ILI.FK_StoveNbr
AND ILI.FK_InvoiceNbr = I.InvoiceNbr
AND I.FK_CustomerID = C.CustomerID
AND C.Country = 'CAN')
GROUP BY S.Type;
But this query doesn't:
SELECT
CAST(S.Type as CHAR (10)) AS 'Stove Type',
COUNT(DISTINCT C.StateProvince) AS 'Number of Sales'
FROM STOVE S, INV_LINE_ITEM ILI, INVOICE I, CUSTOMER C
WHERE S.SerialNumber = ILI.FK_StoveNbr
AND ILI.FK_InvoiceNbr = I.InvoiceNbr
AND I.FK_CustomerID = C.CustomerID
AND C.Country = 'USA'
AND S.Type IN
(SELECT CAST(S.Type as CHAR (10))
FROM STOVE S, INV_LINE_ITEM ILI, INVOICE I, CUSTOMER C
WHERE S.SerialNumber = ILI.FK_StoveNbr
AND ILI.FK_InvoiceNbr = I.InvoiceNbr
AND I.FK_CustomerID = C.CustomerID
AND C.Country = 'CAN')
GROUP BY S.Type;
Instead it produces this:
Stove Type Number of Sales
---------- ---------------
FiredNow 8
And I'm not really sure why. I had thought that I only edited the S.Type in Line 10 to match the S.Type in Line 2 but it's clear that more is happening.
Because "FiredAlways" is over the 10 characters you are casting to.
精彩评论