|
hello ,
i having a problem in SQL.
i will explain my problem with a simple example.
lets say i have a table called "MyTable" with the following headers:
SKU , TITLE , USER , PRICE , QUANTITY , TOTAL_PRICE
the values are:
avs123 ,some title , udikantz , 2.0 , 1 , 2.0
avs123 ,some title , codeproject , 2.0 , 10 , 20.0
avs123 ,some title , baboon , 2.0 , 5 , 10.0
lol22 , some title2 , hehe , 5.0 , 2 , 10.0
lol22 , some title2 , hoho , 5.0 , 1 , 5.0
i am trying to write a query that will output the following table:
SKU TITLE PRICE QUANTITY TOTAL_PRICE
------------------------------------------------------
avs123 some title 2.0 16 32
lol22 some title2 5.0 3 15
so what im trying to do exactly is to group all the SKU items with the same SKU string to one row and Sum the quantity of all the grouped SKUS
I was trying to manage that by using the distinct statement...
Net
|
|
|
|
|
You could try a GROUP BY and some aggregate functions.
SELECT SKU, TITLE, AVG(PRICE), SUM(QUANTITY), SUM(TOTAL_PRICE)<br />
FROM MyTable<br />
ORDER BY SKU, TITLE
|
|
|
|
|
if i understand what u are saying you probably ment:
SELECT SKU, TITLE, AVG(PRICE), SUM(QUANTITY), SUM(TOTAL_PRICE)
FROM MyTable
Group BY SKU, TITLE
and not
...
...
...
ORDER BY SKU, TITLE
well that is not working for me , Cause my table contain a few more fields i didnt mention in the example ,
more headers like : COLOR , SIZE , COMMENTS ,...
which may be diferent in any ROW
so when im doing
...
...
...
...
Group BY COLOR,SIZE,TITLE,SKU....ect
im getting the same table as "MyTable"
any other idea?
Net
|
|
|
|
|
udikantz wrote: Group BY SKU, TITLE
and not
ORDER BY SKU, TITLE
Oops! My bad.
udikantz wrote: well that is not working for me , Cause my table contain a few more fields i didnt mention in the example
Perhaps you should have done.
|
|
|
|
|
hey ,
is it possible to set the cells of a DataGridView so if the content
of a cell is longer than a certain amount of chars ,
the cell will split the text into few lines as needed...
as for now i get cells with long lines and i didnt find any way
to adjust the cells as multiline cells , im sure there is a way of doing that.
what properties , methods would solve my problem?
thanks.
Net
|
|
|
|
|
Have you set the cells to wrap = true
|
|
|
|
|
Hi all,
When i use order by clause in my query, it retrieves data like.....
NULL
NULL
NULL
4/10/2007
4/28/2007
5/17/2007
7/20/2007
9/10/2007
But i need it in another order, like..........
4/10/2007
4/28/2007
5/17/2007
7/20/2007
9/10/2007
NULL
NULL
NULL
Can anyone help me with this...............
thanks in advance.!
Sebastian
|
|
|
|
|
use order by desc
I Love SQL
|
|
|
|
|
Hi
thanks for your reply....but that will not work, If i use that i will get result like.....
9/10/2007
7/20/2007
5/17/2007
4/28/2007
4/10/2007
NULL
NULL
NULL
But i need result in the following order.......
4/10/2007
4/28/2007
5/17/2007
7/20/2007
9/10/2007
NULL
NULL
NULL
I hope you understood..... awaiting for your comments
Thanks once again
Sebastian
|
|
|
|
|
You could try using COALESCE (sp?) in your ORDER BY clause to replace the nulls with a far off date
ORDER BY COALESCE(MyDateColumn, '9999-12-31')
You might want to check what the maximum date permitted is in SQL Server as I don't know off the top of my head. It changes between the DATETIME and SMALLDATETIME types also.
|
|
|
|
|
Hi,
Thank you, thank you very much....that really works fine now. As you said the maximum value for datetimefield is December 31, 9999 and smalldatetime is June 6, 2079.
Best Regards
Sebastian
|
|
|
|
|
|
is this what you are looking for ....
select case when c1 = '31-dec-9999' then null else c1 end as c1 <br />
from (<br />
select top 1000 * <br />
from (<br />
select case when c1 is null then '31-dec-9999' else c1 end as c1 from a <br />
)a order by c1<br />
)a
i've checked this query with following sample table and data
CREATE TABLE a (c1 datetime)
insert into a values('01-jan-07')
insert into a values(null)
insert into a values('21-jan-07')
insert into a values('10-jan-07')
insert into a values('11-apr-07')
insert into a values(null)
insert into a values('10-apr-07')
insert into a values(null)
insert into a values('30-apr-07')
and output is
2007-01-01 00:00:00.000
2007-01-10 00:00:00.000
2007-01-21 00:00:00.000
2007-04-10 00:00:00.000
2007-04-11 00:00:00.000
2007-04-30 00:00:00.000
NULL
NULL
NULL
Regards
KP
|
|
|
|
|
Hi, thanks Kp, i have solved the issue.....
sebastian
|
|
|
|
|
you welcome.
colin's solution was simple
Regards
KP
|
|
|
|
|
Any help would be aoppreciated. I'm trying to pass the login username information into a sql table filter on column containing names. I have tried WHERE statement but can't find how pick-up user name.
Steve
|
|
|
|
|
sjp700 wrote: Any help would be aoppreciated. I'm trying to pass the login username information into a sql table filter on column containing names. I have tried WHERE statement but can't find how pick-up user name.
Some more information would be helpful. The SQL code you are currently using. The appropriate code in the application that calls the SQL also would be useful also.
|
|
|
|
|
Hi all. I'm wondering if there is a means in SQL to delete a certain number of rows from a table, when ordered a certain way. In other words, I want to be able to say something like this:
delete the 10 oldest rows where <some condition> applies
...any thoughts?
I'm looking for something in SQL; I can do it fine with ADO.NET but ADO.NET is too slow and need something faster.
|
|
|
|
|
logan1337 wrote: Hi all. I'm wondering if there is a means in SQL to delete a certain number of rows from a table, when ordered a certain way. In other words, I want to be able to say something like this:
delete the 10 oldest rows where <some condition=""> applies
How do you define which are the oldest rows? Once you have the answer to that question you can add an ORDER BY clause and then a TOP 10
|
|
|
|
|
That is easy, just order by a certain date column, in ascending.
I've done a bit of reasearching this TOP command, but from what I understand, you in fact cannot use ORDER BY with TOP when deleting. According to the documentation on MSDN, TOP simply selects n random rows at a time, and is really only intended for splitting up delete operations over several transactions.
Can anyone verify this? It would be nice to be able to delete n rows off the top of a sorted table.
Logan
|
|
|
|
|
You can do this:
DELETE<br />
FROM MyTable<br />
WHERE ID IN (SELECT TOP 10 ID FROM MyTable ORDER BY HistoricalDate)
If you have any other conditions with which to filter the data to be deleted then they should be put in the subquery.
|
|
|
|
|
Ah yes! Of course, thanks! I was so caught up on reading that it couldn't be used with deletes that I didn't realize you could delete using a select! 
|
|
|
|
|
Hello,
VS 2005
Using untyped dataset.
I have a xml file which I am using for local storage.
I have a dialog box that displays text boxes for uses to add another new incident.
I read in the xml file and add the following code to set the auto increment properties of the dataset table.
However, everytime the user wants to add a new incident the auto-increment properties will be set again and again. So the new row will not be incremented and will always have the same incidentID PK value.
Is there a way to only set these only once, so that when the add incident form loads it won't re-set the auto-increment properties. I was thinking of having some sort of global untyped dataset.
My code is below:
many thanks for any advice on this.
<br />
'Read the xsd file<br />
dsIncidents.ReadXmlSchema(incidentXSDpath)<br />
'Read in the xml fil<br />
dsIncidents.ReadXml(incidentXMLpath, XmlReadMode.DiffGram)<br />
<br />
Dim dt As DataTable = dsIncidents.Tables(0)<br />
'Set the auto increment properties<br />
dt.Columns("IncidentID").AutoIncrement = True<br />
dt.Columns("IncidentID").AutoIncrementSeed = 0<br />
dt.Columns("IncidentID").AutoIncrementStep = -1<br />
<br />
Dim dr As DataRow = dt.NewRow()<br />
dr("Company") = Me.cboCustomer.Text<br />
dr("Contact") = Me.cboContact.Text<br />
dr("PhoneNo") = Me.txtPhone.Text<br />
dt.Rows.Add(dr)<br />
<br />
'Write to the xml and schema files<br />
dsIncidents.WriteXmlSchema(incidentXSDpath)<br />
dsIncidents.WriteXml(incidentXMLpath, XmlWriteMode.DiffGram)<br />
|
|
|
|
|
i write PROCEDURE to update record in datadate this PROCEDURE have where statment ,,,
mow how can if the PROCEDURE make update successfully to database or not ???
Palestine
|
|
|
|
|
now how can know if the PROCEDURE make update successfully to database or not ???
Palestine
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
