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 occurances in sql

Hi,

I am trying to count the times a machine has been scanned in to a table. what happens is everytime a product is read at a station it is entered in to the table with the following data

stationid
modelnumber
serialnumber

i want to establish a list of every machine that has been read at station id 15 but not station id 2 or 5, does this make sense? basically to establish what machines are not being read at points a and b but end up at point c.

obviously to do this i need to check the model and serials throughout the table to see if there is another occurance of it against station 2 or 5.

i have done this before but cannot recreate it for the lfie of me!

all help is greatly welcomed :)

Quote:

Originally Posted by 692909824

Hi,

I am trying to count the times a machine has been scanned in to a table. what happens is everytime a product is read at a station it is entered in to the table with the following data

stationid
modelnumber
serialnumber

i want to establish a list of every machine that has been read at station id 15 but not station id 2 or 5, does this make sense? basically to establish what machines are not being read at points a and b but end up at point c.

obviously to do this i need to check the model and serials throughout the table to see if there is another occurance of it against station 2 or 5.

i have done this before but cannot recreate it for the lfie of me!

all help is greatly welcomed :)

Your script should be:

SELECT *
FROM [TABLE]
WHERE serialno NOT IN
(SELECT serialno
FROM [TABLE]
WHERE stationid = 2 or stationid = 5)
ANDstationid =15

Could anyone intimate with MS Reporting Services help? MSFT? MVP

Is it true that the Full Product of SQL Server Reporting Services is
available for download.....LICENSE -FEE- FREE?! I just want to verify that
there are no "catch" or "hidden" fees or license fees that I have overlooked
(beyond the Reporting Services web-site). As long as I have at least one
licensed version of SQL Server 2000 Stand., Ent.
etc................Reporting Services is free'?
Thank you for any verification.That is correct. However to create reports you will need Visual Studio
.NET. Any edition, even the smallest one, like Visual Studio .NET VB.
"Me" <Me@.discussions.microsoft.com> wrote in message
news:0F6D8668-EB82-44FE-AF65-B074ED09E49F@.microsoft.com...
> Is it true that the Full Product of SQL Server Reporting Services is
> available for download.....LICENSE -FEE- FREE?! I just want to verify
that
> there are no "catch" or "hidden" fees or license fees that I have
overlooked
> (beyond the Reporting Services web-site). As long as I have at least one
> licensed version of SQL Server 2000 Stand., Ent.
> etc................Reporting Services is free'?
> Thank you for any verification.|||Thank you for your help.
"AshVsAOD" wrote:
> That is correct. However to create reports you will need Visual Studio
> ..NET. Any edition, even the smallest one, like Visual Studio .NET VB.
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:0F6D8668-EB82-44FE-AF65-B074ED09E49F@.microsoft.com...
> > Is it true that the Full Product of SQL Server Reporting Services is
> > available for download.....LICENSE -FEE- FREE?! I just want to verify
> that
> > there are no "catch" or "hidden" fees or license fees that I have
> overlooked
> > (beyond the Reporting Services web-site). As long as I have at least one
> > licensed version of SQL Server 2000 Stand., Ent.
> > etc................Reporting Services is free'?
> >
> > Thank you for any verification.
>
>|||It's not quite that simple. Is it ever? :-) The following is from
http://www.microsoft.com/sql/reporting/howtobuy/faq.asp:
Q. Is Reporting Services free?
A. No. Reporting Services is part of the SQL Server 2000 license. If
you have a licensed copy of SQL Server 2000, you may run Reporting Services
on the same server for no additional license fee. If you want to run
Reporting Services on a server that is not licensed for SQL Server 2000, you
will need to obtain a SQL Server 2000 license for that additional server.
This is the same licensing model used for SQL Server Analysis Services. For
more information about licensing scenarios, see the How to License Reporting
Services page.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message
news:eB7r4smhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> That is correct. However to create reports you will need Visual Studio
> .NET. Any edition, even the smallest one, like Visual Studio .NET VB.
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:0F6D8668-EB82-44FE-AF65-B074ED09E49F@.microsoft.com...
>> Is it true that the Full Product of SQL Server Reporting Services is
>> available for download.....LICENSE -FEE- FREE?! I just want to verify
> that
>> there are no "catch" or "hidden" fees or license fees that I have
> overlooked
>> (beyond the Reporting Services web-site). As long as I have at least one
>> licensed version of SQL Server 2000 Stand., Ent.
>> etc................Reporting Services is free'?
>> Thank you for any verification.
>
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end|||Thank you very much for that verification.....!
MSFT feedback is very much appreciated.
"Stephen Dybing [MSFT]" wrote:
> It's not quite that simple. Is it ever? :-) The following is from
> http://www.microsoft.com/sql/reporting/howtobuy/faq.asp:
> Q. Is Reporting Services free?
> A. No. Reporting Services is part of the SQL Server 2000 license. If
> you have a licensed copy of SQL Server 2000, you may run Reporting Services
> on the same server for no additional license fee. If you want to run
> Reporting Services on a server that is not licensed for SQL Server 2000, you
> will need to obtain a SQL Server 2000 license for that additional server.
> This is the same licensing model used for SQL Server Analysis Services. For
> more information about licensing scenarios, see the How to License Reporting
> Services page.
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "AshVsAOD" <.> wrote in message
> news:eB7r4smhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> > That is correct. However to create reports you will need Visual Studio
> > .NET. Any edition, even the smallest one, like Visual Studio .NET VB.
> > "Me" <Me@.discussions.microsoft.com> wrote in message
> > news:0F6D8668-EB82-44FE-AF65-B074ED09E49F@.microsoft.com...
> >> Is it true that the Full Product of SQL Server Reporting Services is
> >> available for download.....LICENSE -FEE- FREE?! I just want to verify
> > that
> >> there are no "catch" or "hidden" fees or license fees that I have
> > overlooked
> >> (beyond the Reporting Services web-site). As long as I have at least one
> >> licensed version of SQL Server 2000 Stand., Ent.
> >> etc................Reporting Services is free'?
> >>
> >> Thank you for any verification.
> >
> >
>
>