Count question

Hi DBA's -

Kindly help me figure out the following

My data looks like this -

Month Product Brand Revenue
-- --- --- ---
Jan A x 10
Jan A y 20
Jan B z 30

A report from the above data would be

Revenue for Jan = 60 and Product count = 2.

So I figure, I would need

Month Product Brand Revenue Flag
-- --- --- --- --
Jan A x 10 1
Jan A y 20 0 (since A is counted)
Jan B z 30 1

I want to count only the first occurence of the product in the Flag column.

Is there a way to do this.

- Vivekselect Month,
sum(Revenue),
Count(distinct Product)
from YourTable
group by Month|||select [month],
productcount=count(distinct product),
totalrevenue=sum(revenue)
from your_table
group by [month]|||hey, I just didn't click on Submit, because there other things in life, like phone calls!!!!|||Phone calls?

That's OLD TECHNOLOGY...

Your code was more complete, anyway. I confess I was being a little lazy...|||But did you notice the snippets are almost identical? This is earie...|||You are just saying that to be nice. Yours was much more colorful than mine as well. I simply lack your aesthetic sense of code.

Mine was pathetic. A shoddy hack of garbled syntax totally lacking in character or depth. In my haste to post, I neglected that which makes code enjoyable and pleasing to the senses.

I am truly ashamed.

Wait a minute... "Groupby"?

Hey! "Group by" is two words, not one! That won't even compile, much less execute!

Hmmph! Well. I guess I feel better now. :)|||Now who's a real hoot? :Dsql

count 2 value in a table

hi all pls kindly help
need to know how to do a count for 2 value in a column

example :
my table(entries)
has the field name 'selection'
under this selection, data inside have is e.g(apple,apple,pear,pear,pear,orange)
i need to do a COUNT on how many apple and pear there is inside this table

but i seriously have no idea, pls kindly help thanksThis sounds like homework from an "Introduction to databases" course, but I'm willing to give you the benefit of the doubt...SELECT Count(*), selection
FROM entries
GROUP BY selection
ORDER BY Count(*) DESC -- gratis-PatP|||SELECT Count(*), selection
FROM entries
GROUP BY selection
ORDER BY Count(*) DESC -- gratis

sorry for my ignorance..
wat u meant by DESC over here??

wat i want is to count how many pears and apple there is in that table but not orange|||DESC means descending, and you don't have to use it if you don't want to

did you want the total of apples and pears? that's different --

SELECT count(*) as applesandpears
FROM entries
WHERE selection in ('apple','pear')|||thanks so much...
really so basic

need to read up my books again :(|||Sorry, my background is showing :rolleyes:

Gratis is a Latin vulgate term that means "free of charge" or "complimentary". When I used it in a comment, I meant that I expected it to make things easier to use, without actually impacting the solution at all.

As Rudy pointed out, in this case it simply orders the result set so that the most frequently occuring items appear at the top of the list. You can safely ignore it if it doesn't help you.

-PatP|||ok got it ..
but still appreciated
:)

Could anyone kindly tell me,What tool can I use to Create db or restore db or backup db in SQL 2

Could anyone kindly tell me,What tool can I use to Create db or restore db or backup db in SQL 2005 Express?
I DownLoad the tool named XM.exe,but It's only a Query tool and I have to do everything using scripts.I realy want to know Is there a tool that I can use to Create db and restore db and backup db and create table etc. like the Admination tools in SQL 2000 ?

Thanks for u kindness.

Tonny.

You can use the tools that come with the June CTP Developer Edition.

|||

Thanks for ur kindness ,and Is it a single tool or integrated with the SQL2005 TCP Developer Version?Is it free to download?If yes, Could u tell me where Can I Download the tool or Where Can I DownLoad the SQL 2005 TCP Developer Version?

Thank u again.

Tonny.

|||

SQL Server Express Edition did not originally ship with any type of Management Tools. After SQL Server 2005 Service Pack 1, you have the option to download SQL Server Management Studio Express with SQL Server Express Edition with Advanced Services.

Compare the new offerings: http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Could anyone kindly tell me,What tool can I use to Create db or restore db or backup db in S

Could anyone kindly tell me,What tool can I use to Create db or restore db or backup db in SQL 2005 Express?
I DownLoad the tool named XM.exe,but It's only a Query tool and I have to do everything using scripts.I realy want to know Is there a tool that I can use to Create db and restore db and backup db and create table etc. like the Admination tools in SQL 2000 ?

Thanks for u kindness.

Tonny.

You can use the tools that come with the June CTP Developer Edition.

|||

Thanks for ur kindness ,and Is it a single tool or integrated with the SQL2005 TCP Developer Version?Is it free to download?If yes, Could u tell me where Can I Download the tool or Where Can I DownLoad the SQL 2005 TCP Developer Version?

Thank u again.

Tonny.

|||

SQL Server Express Edition did not originally ship with any type of Management Tools. After SQL Server 2005 Service Pack 1, you have the option to download SQL Server Management Studio Express with SQL Server Express Edition with Advanced Services.

Compare the new offerings: http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/