count inside case?!

Question

count inside case?!

Terry-380449

Default port

Points: 1458
More actions
August 6, 2007 at 8:19 am

#197138

Hello,
I have a table with the following data: store, articles
I need to know how many articles in each store, in one sql query.
I know we can write smthg like:
select count(articles) from mystores group by store
But then i can't manipulate the data i get in my query! I am not writing t-sql, just a query.
I need to know for a given store the number or articles concerned.
Is there a way with CASE for example? Smthg like:
SELECT store, count(articles) ,
CASE store
      WHEN '1' THEN store1count = store1count + 1
      WHEN '2' THEN store2count = store2count + 1
      ELSE 0
END
"total" FROM mystores
how can i define store1count and store2count in my query... remember , i can't use t-sql...
tx for helping!

Viewing 11 posts - 1 through 10 (of 10 total)

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

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

Use Sum(Case when x=y then 1 else 0 End) as xxx

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

bledu SSChampion Points: 12335 More actions · Answer 2

Terry,

your first solution should give you what you are asking for.

select count(articles) from mystores group by store

what exactly do you mean by

But then i can't manipulate the data i get in my query! I am not writing t-sql, just a query.

Everything you can imagine is real.

Terry-380449 Default port Points: 1458 More actions · Answer 3

this could be good, but it will give me results under the following format:

st1count st2count st3count

article1 10 0 0

article2 0 20 0

article3 0 0 30

while i need smthg like:

count

article1 10

article2 20

article3 30

I don't know if this is possible, actually in my case each store sells one kind of article....

bledu SSChampion Points: 12335 More actions · Answer 4

so is your question then more like:

i would like a count of the stores that [sell/buy/whatever] an articles

Everything you can imagine is real.

bledu SSChampion Points: 12335 More actions · Answer 5

are you saying you have a one to one mapping of the store to the article?

Everything you can imagine is real.

Terry-380449 Default port Points: 1458 More actions · Answer 6

Actually, I think i complicated the question

I would like a count of the articles, grouped by article, no matter the store... but if i do a simple select count, i will get the results like:

article1 10

article2 20

article3 30

but how can i use the result i get if i don't assign each count result into a variable name?

Here's a code example (with some js), where i execute my query for one type of article only.

var myQuery= xtk.queryDef.create(

</select>

<where>

</where>

</query>

);

var resultSet1 = myQuery.ExecuteQuery();

logInfo ("nb of articles1:" + resultSet1.somethinghere);

Right now, i am writing this code for as mnay articles as I have.

I am not able to do it dynamically.

I hope i was clear this time.

In all cases thanks again!

bledu SSChampion Points: 12335 More actions · Answer 7

are you developing a web app?

Everything you can imagine is real.

Terry-380449 Default port Points: 1458 More actions · Answer 8

Terry-380449

Default port

Points: 1458

August 6, 2007 at 10:11 am

#724900

it's a rich client application

bledu SSChampion Points: 12335 More actions · Answer 9

well i think you can use ADODB. can you instantiate it?

Everything you can imagine is real.

Terry-380449 Default port Points: 1458 More actions · Answer 10

mmmm not sure... it's a pre-written application, and I can't change much in the core code.

Can you give an example of what it would be?

tx!