Skip to main content
gamedev.net
🔒 Locked

[web] SQL - COUNT() Help

Started by NewbieA Oct 24, 2005 at 10:54 AM 4 replies 1.2k views
Original Post
NewbieA
NewbieA
Hello, Is it possible to assign things that return by COUNT into a variable? Let's say i have a table like this: table_1: 

Name        Group        Grade      Year
=============================================
Peter       A            A          2005
Marry       A            B          2005
Susan       B            A          2005
Carol       B            B          2005
And I wanted to see how many people in Group A and Group B in a table like this: table_2: 

Group A        Group B
======================
2              2
I have no idea how to do this, in a quick and easy way. So i break it down into functions. Breaking into selecting Group A and B, then INSERT them into the table... But I am stuck at stage 1 [bawling] i have this: 

FUNCTION myFunction(myGroup)
result = SELECT COUNT(*) FROM table_1 WHERE Group = 'A'
RETURN result
ENDFUNC
But I CAN'T... why? [dead] any way round it? [help] Help~~~ Thanks in advance [smile] (Oh, I forgot to say I'm using Foxpro 9...)
evolutional
evolutional
declare @result int

select @result = COUNT(*) FROM table_1 WHERE Group = 'A'
Kylotan
Kylotan
If you say "I can't", it usually helps to say what is actually happening. :)

In theory I am guessing that your result should be a table like this:

Count(Group)------------22


You may need to find some way of selecting the column name. I don't know how to do that in Foxpro. You may have to compile the table one row at a time.
evolutional
evolutional
Doesn't foxpro allow group by statements?

select Group, count(*)
from table_1
group by Group

If not, it sucks.
kodeninja
kodeninja
I may be misunderstanding the problem a little, but couldn't you just use "AS"?
For example:
SELECT COUNT(*) AS Group_Count FROM table_1 WHERE Group = 'A'
Creates a column called Group_Count which you can query from the result.
You can replace Group_Count with anything you want as long as is valid name (ie. if it was an actual column).

[EDIT]
Sorry, I re-read it and realized I did misunderstand. evolutional is correct you need to use a GROUP BY directive. for example (mixed with what I said above):

SELECT Group, COUNT(*) AS GroupCnt FROM table_1 GROUP BY Group;

will return
+-------+----------+| Group | GroupCnt |+-------+----------+| A     |        2 || B     |        2 |+-------+----------+


By the way, I'm surprised you have a column called "Group", I mean if your careful then no problem, but i could see that causing some odd bugs later on :P
[/EDIT]

[Edited by - kodeninja on October 24, 2005 11:28:55 AM]
NewbieA
NewbieA
Thanks guys, I now kinda got pass the problem...

And thanks kodeninja for reminding me not to use "group" as a column name...

Thanks [smile]

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.