Baffled by query--complete

Question

Post reply

Baffled by query--complete

Elliott Berkihiser

SSCarpal Tunnel

Points: 4068
More actions
November 17, 2007 at 9:21 am

#62883

Morning everyone,
I am having trouble writing a single query to pull together some business statistics. Here's a simplified analogy of the problem:
A produce seller markets five items: Bananas, apricots and kiwis are in the Fruit marketing group; broccoli and spinach are in the Vegetable marketing segment. A Sales table consists of the sales of each item for each business day>
Kiwi 1/1/2007 634
Spinach 1/1/2007 101
Banana 1/1/2007 353
Kiwi 1/2/2007 406
Broccoli 2/12/2007 34
Spinach 2/12/2007 722
Banana 2/13/2007 827
...
I would like to produce the following output with a single query:
MarketingGroup Year-To-Date Month-To-Date
Fruit 1004 323
Vegetable 9244 999
The following query produces the YTD sales for the two groups but fails when I extend it to try to capture Month to Date or the last 30 days (by marketing group)
Select
CASE
WHEN ProduceItem IN('Kiwi','Apricot','Banana') THEN
'FRUIT'
WHEN ProduceItem IN('Broccoli','Spinach') THEN
'VEGETABLE'
END,
SUM(DailySales), --produces YTD by group
CASE
WHEN ProduceItem IN('Kiwi','Apricot','Banana') THEN
(Select SUM(DailySales) From Sales WHERE Sales.Day
BETWEEN '2/1/07' and '2/18/07' --illustration
AND ProduceItem IN('Kiwi','Apricot','Banana'))
WHEN ProduceItem IN('Broccoli','Spinach') THEN
(Select SUM(DailySales) From Sales WHERE Sales.Day
BETWEEN '2/1/2007 AND '2/18/2007'
AND ProduceItem IN('Broccoli','Spinach'))
FROM Sales
WHERE ProduceItem IN('Kiwi','Apricot',Banana','Broccoli','
Spinach') -- there are other items
GROUP BY
CASE
WHEN ProduceItem IN('Kiwi','Apricot','Banan') THEN
'Fruit'
WHEN ProduceItem IN('Broccoli','Spinach') THEN
'Vegetable'
END
This query produces the error, "ProduceItem must appear in either the Select list or the Group By clause"
Since it appears in both, I am baffled by how to do this.
Thanks,
Elliott
AND ProduceItem IN('Broccoli','Spinach'))

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

You misspelled "Banana" as "Banan"

My recommendation would be to first make a lookup table that identifies which produce item is what. Then, try again...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HansVE Mr or Mrs. 500 Points: 524 More actions · Answer 2

SELECT g.MarketingGroup, SUM(s.DailySales) [Year-To-Date],

SUM(CASE WHEN s.Day >= '20070201' AND s.Day < '20070301' THEN s.DailySales END) [Month-To-Date]

FROM dbo.Sales s JOIN

(SELECT 'Kiwi' ProduceItem, 'FRUIT' MarketingGroup

UNION ALL SELECT 'Apricot', 'FRUIT'

UNION ALL SELECT 'Banana', 'FRUIT'

UNION ALL SELECT 'Broccoli', 'VEGETABLE'

UNION ALL SELECT 'Spinach', 'VEGETABLE') g ON s.ProduceItem = g.ProduceItem

GROUP BY g.MarketingGroup

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 3

Thanks Jeff and Hans,

It looks to me like Hans' solution is equivalent to Jeff's in that Hans makes a lookup table on the fly. I chose to make a permanent lookup table (it will be used for other queries) and the desired result was easy to obtain.

Cheers,

Elliott

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

Thanks for the feedback, Elliot...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)