help

[web7 via SQLMonster.com]

TABLE Inventory has fields:
company,area,store,dept,Item,Inv

How to get counts when Inv for each area is not zero. In my solution i create
a #temp1,
find sum(Inv) as smInv then i do 'count (case when smInv > 0 then 1 end)'. it
works but seems like 2 or 3 extra steps.looking for elegant query to do it.

another thing is that, when i do something like:
select company , area, store, sum(Inv), case when sum(Inv)> 0 then ....

In here again i repeat the sum(Inv) two or more times in the same line or
query. How to just use it once. i get error here: select company , area,
store, sum(Inv) as mysum, case when mysum > 0 then ....
i use server 2000.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200712/1

[Stuart Ainsworth]

I'm sorry, it seems like you've got a couple of different questions
going on. Here's a couple of stabs at helping you, but you're
probably going to have to fill in some details.

> TABLE Inventory has fields:
> company,area,store,dept,Item,Inv
>
> How to get counts when Inv for each area is not zero. In my solution i create
> a #temp1,
> find sum(Inv) as smInv then i do 'count (case when smInv > 0 then 1 end)'. it
> works but seems like 2 or 3 extra steps.looking for elegant query to do it.
>

Do you mean:

SELECT company, area, store, dept, item, SUM(Inv)
FROM Inventory
WHERE Inv >0
GROUP BY company, area, store, dept, item

?

> another thing is that, when i do something like:
> select company , area, store, sum(Inv), case when sum(Inv)> 0 then ....
>
> In here again i repeat the sum(Inv) two or more times in the same line or
> query. How to just use it once. i get error here: select company , area,
> store, sum(Inv) as mysum, case when mysum > 0 then ....
> i use server 2000.
>
> --

SQL Server doesn't allow you to use aliases in the same query (unlike
Microsoft Access for example). But I'm not sure why you need the CASE
expression, in the first place, so this question confuses me.

Stu

[web7 via SQLMonster.com]

no, i need total count when an item's Inv in an area is not zero.

Stuart Ainsworth wrote:
>I'm sorry, it seems like you've got a couple of different questions
>going on. Here's a couple of stabs at helping you, but you're
>probably going to have to fill in some details.
>
>> TABLE Inventory has fields:
>> company,area,store,dept,Item,Inv

>[quoted text clipped - 3 lines]

>> find sum(Inv) as smInv then i do 'count (case when smInv > 0 then 1 end)'. it
>> works but seems like 2 or 3 extra steps.looking for elegant query to do it.
>
>Do you mean:
>
>SELECT company, area, store, dept, item, SUM(Inv)
>FROM Inventory
>WHERE Inv >0
>GROUP BY company, area, store, dept, item
>
>?
>
>> another thing is that, when i do something like:
>> select company , area, store, sum(Inv), case when sum(Inv)> 0 then ....

>[quoted text clipped - 5 lines]

>>
>> --
>
>SQL Server doesn't allow you to use aliases in the same query (unlike
>Microsoft Access for example). But I'm not sure why you need the CASE
>expression, in the first place, so this question confuses me.
>
>Stu

--
Message posted via http://www.sqlmonster.com

[Hugo Kornelis]

On Mon, 03 Dec 2007 04:29:19 GMT, web7 via SQLMonster.com wrote:

>no, i need total count when an item's Inv in an area is not zero.

Hi web7,

Please post your table structure as a CREATE TABLE statement (including
all contraints, properties, and indexes, though you may omit irrelevant
columns), some well-chosen rows of sample data (as INSERT statements) to
illustrate the problem, and the expected results.

That will help us understand what you need to do, and enable us to test
solutions before posting instead of playing a guessing game.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[web7 via SQLMonster.com]

This is a sample of a could be a bigger table with city1, city2,... store1,
store2,store3,... , dept1,dept2, dept3,....,etc.

Table Inventory:
country, city, store, dept, item, Inventory
country1 city1 store1 dept1 Itme1 23
country1 city1 store1 dept1 Itme2 0
country1 city1 store1 dept1 Itme2a 0
country1 city1 store1 dept2 Itme3 0
country1 city1 store1 dept2 Itme4 0
country1 city1 store2 dept1 Itme1 0
country1 city1 store2 dept1 Itme2 0
country1 city1 store2 dept2 Itme3 0
country1 city1 store2 dept2 Itme4 0
country1 city1 store2 dept2 Itme4a 0

result to look like:
country1 city1 dept1 1
country1 city1 dept2 2

shows count of when all Items in a deptarment is zero in different stores
grouped by city. In other words, Like how many stores in a given city its
dept1 is empty of all its items. Forgive me if this is confusing.

Hugo Kornelis wrote:
>>no, i need total count when an item's Inv in an area is not zero.
>
>Hi web7,
>
>Please post your table structure as a CREATE TABLE statement (including
>all contraints, properties, and indexes, though you may omit irrelevant
>columns), some well-chosen rows of sample data (as INSERT statements) to
>illustrate the problem, and the expected results.
>
>That will help us understand what you need to do, and enable us to test
>solutions before posting instead of playing a guessing game.
>

--

[Hugo Kornelis]

On Thu, 06 Dec 2007 07:30:27 GMT, web7 via SQLMonster.com wrote:

>This is a sample of a could be a bigger table with city1, city2,... store1,
>store2,store3,... , dept1,dept2, dept3,....,etc.
>
>Table Inventory:
>country, city, store, dept, item, Inventory
>country1 city1 store1 dept1 Itme1 23
>country1 city1 store1 dept1 Itme2 0
>country1 city1 store1 dept1 Itme2a 0
>country1 city1 store1 dept2 Itme3 0
>country1 city1 store1 dept2 Itme4 0
>country1 city1 store2 dept1 Itme1 0
>country1 city1 store2 dept1 Itme2 0
>country1 city1 store2 dept2 Itme3 0
>country1 city1 store2 dept2 Itme4 0
>country1 city1 store2 dept2 Itme4a 0
>
>result to look like:
>country1 city1 dept1 1
>country1 city1 dept2 2
>
>shows count of when all Items in a deptarment is zero in different stores
>grouped by city. In other words, Like how many stores in a given city its
>dept1 is empty of all its items. Forgive me if this is confusing.

Hi web7,

Please post your table structure as a CREATE TABLE statement (including
all contraints, properties, and indexes, though you may omit irrelevant
columns), some well-chosen rows of sample data (as INSERT statements) to
illustrate the problem, and the expected results.

That will help us understand what you need to do, and enable us to test
solutions before posting instead of playing a guessing game.

If you need help in putting together the CREATE TABLE and INSERT
statements, then start at www.aspfaq.com/5006.

[web7 via SQLMonster.com]

you mean, like:

create table #temp1(countryvar nvarchar(30), cityvar nvarchar(30), storevar
nvarchar(30), deptvar nvarchar(30), Itmevar nvarchar(30), #inv int)

insert into #temp1 values('country1', 'city1', 'store1', 'dept1', 'Itme1', 23)
;
insert into #temp1 values('country1', 'city1', 'store1', 'dept1', 'Itme2', 0)
;
insert into #temp1 values('country1', 'city1', 'store1', 'dept1', 'Itme2a', 0)
;
insert into #temp1 values('country1', 'city1', 'store1', 'dept1', 'Itme3', 0)
;
insert into #temp1 values('country1', 'city1', 'store1', 'dept2', 'Itme4', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept1', 'Itme1', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept1', 'Itme2', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept3', 'Itme3', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept2', 'Itme4', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept2', 'Itme4a', 0)
;

/*
select * from #temp1
*/

drop table #temp1

result to look like:
country1 city1 dept1 1
country1 city1 dept2 2

shows count of when all Items in a deptarment is zero in different stores
grouped by city. In other words, Like how many stores in a given city its
dept1 is empty of all its items. Forgive me if this is confusing.

Hugo Kornelis wrote:
>>This is a sample of a could be a bigger table with city1, city2,... store1,
>>store2,store3,... , dept1,dept2, dept3,....,etc.

>[quoted text clipped - 19 lines]

>>grouped by city. In other words, Like how many stores in a given city its
>>dept1 is empty of all its items. Forgive me if this is confusing.
>
>Hi web7,
>
>Please post your table structure as a CREATE TABLE statement (including
>all contraints, properties, and indexes, though you may omit irrelevant
>columns), some well-chosen rows of sample data (as INSERT statements) to
>illustrate the problem, and the expected results.
>
>That will help us understand what you need to do, and enable us to test
>solutions before posting instead of playing a guessing game.
>
>If you need help in putting together the CREATE TABLE and INSERT
>statements, then start at www.aspfaq.com/5006.
>

--

[Erland Sommarskog]

web7 via SQLMonster.com (u39242@uwe) writes:
> you mean, like:
>
> Yes, that's the data. Great. Only one problem left:

> result to look like:
> country1 city1 dept1 1
> country1 city1 dept2 2
>
> shows count of when all Items in a deptarment is zero in different stores
> grouped by city. In other words, Like how many stores in a given city its
> dept1 is empty of all its items. Forgive me if this is confusing.

Could you explain in a little more detail why you arrive at these numbers?
And why is dept3 not present?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[web7 via SQLMonster.com]

create table #temp1(countryvar nvarchar(30), cityvar nvarchar(30), storevar

nvarchar(30), deptvar nvarchar(30), Itemvar nvarchar(30), #inv int)

insert into #temp1 values('country1', 'city1', 'store1', 'dept1', 'Item1', 23)
;
insert into #temp1 values('country1', 'city1', 'store1', 'dept1', 'Item2', 0)
;
insert into #temp1 values('country1', 'city1', 'store1', 'dept1', 'Item3', 0)
;
insert into #temp1 values('country1', 'city1', 'store1', 'dept2', 'Item4', 0)
;
insert into #temp1 values('country1', 'city1', 'store1', 'dept3', 'Item5', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept1', 'Item1', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept1', 'Item2', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept1', 'Item3', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept2', 'Item4', 0)
;
insert into #temp1 values('country1', 'city1', 'store2', 'dept3', 'Item5', 0)
;

this is a small sample of a bigger table. a query should handle any number of
depts, stores,...
Store1 can have more departments than shown or city1 can have more stores
than shown.
query should show for a cityx, number of stores when all items in a given
dept has 0 inventory.

result to look something like:

country1 city1 dept1 1
country1 city1 dept2 2

country1 city1 dept3 2

Erland Sommarskog wrote:
>> you mean, like:
>>
>> Yes, that's the data. Great. Only one problem left:
>
>> result to look like:
>> country1 city1 dept1 1

>[quoted text clipped - 3 lines]

>> grouped by city. In other words, Like how many stores in a given city its
>> dept1 is empty of all its items. Forgive me if this is confusing.
>
>Could you explain in a little more detail why you arrive at these numbers?
>And why is dept3 not present?
>

--

[Erland Sommarskog]

web7 via SQLMonster.com (u39242@uwe) writes:

> this is a small sample of a bigger table. a query should handle any
> number of depts, stores,...
> Store1 can have more departments than shown or city1 can have more stores
> than shown.
> query should show for a cityx, number of stores when all items in a given
> dept has 0 inventory.
>
> result to look something like:
> country1 city1 dept1 1
> country1 city1 dept2 2
> country1 city1 dept3 2

This query gives you the desired result, but you need to test it for a
larger sample to see that I did get it right:

SELECT a.countryvar, a.cityvar, a.deptvar, COUNT(DISTINCT a.storeid)
FROM #temp1 a
WHERE NOT EXISTS (SELECT *
FROM #temp1 b
WHERE a.countryvar = b.countryvar
AND a.cityvar = b.cityvar
AND a.deptvar = b.deptvar
AND a.storevar = b.storevar
AND b.#inv > 0)
GROUP BY a.countryvar, a.cityvar, a.deptvar

If you get incorrect results, please come back with a sample that
illustrates the problem.