|
Below is my query. When I do a count it is counted all location,I want to be have a count after group by executes. How do I do this?
SELECT a.Number, a.Location,count(a.Location)'Number'
FROM Reports_Location
GROUP BY a.Number, a.Location
|
|
|
|
|
Not sure of the meaning. But if you are looking for a count of locations you could try this
select count(*) from (
select unique location from reports_location
)
;
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
Hi all,
I'm a .NET newbie, and just kind of a programming/developing wannabe. I'm moving into C# from a (tiny) background in JavaScript only. I administer a club website in ASP and want to switch to ASP.NET. Here's my conundrum:
I have a membership database (Access) that includes a field for when a record was last updated (UpdDt), and who updated it (UpdLogin). The value in UpdLogin is a member's ID number, correlating to the MbrId field.
I want to show administrators a list of all members that were updated recently, and who updated them. I need to not just show the UpdLogin value, but the actual name of the person who did the update. So I need to (for each record) find the name, within the table I'm already using as the DataSource, and show that as "Updated By".
In ASP, I created 2 recordsets and looped through the first. For every record, I used:
rs2.MoveFirst<br />
rs2.Find("MbrId = "+updLogin)
then (making sure the record existed) grabbed the name.
I think it was inelegant, and probably inefficient, but it worked.
SO! How the heck do I do this in .NET? I'm still working on wrapping my head around DataGrids and DataSets and DataAdapters and DataViews and OhMyGoodnessDoesItEverEnd...
Have I missed some simple SQL solution to this? Do I require a couple of tables in a DataSet, and use some method similar to what I did in ASP?
Please, help a poor newbie in need!
Best Regards,
Kevin Swanson
http://bcmc.ca[^]
|
|
|
|
|
Why don´t you get all the data in a snigle query ?
Free your mind...
|
|
|
|
|
Sheesh. Query. Thanks, Guillermo.
I went into Access, and designed a query. Of course, I didn't realize you could put more than one copy of the same table into the query builder!
Came out with a SQL statement like this:
SELECT Mbr.MbrId, Mbr.LastName, Mbr.FirstName, Mbr.UpdDt, Mbr_1.LastName, Mbr_1.FirstName<br />
FROM Mbr INNER JOIN Mbr AS Mbr_1 ON Mbr.UpdLogin = Mbr_1.MbrId<br />
ORDER BY Mbr.UpdDt DESC;
Well, yeah!
Very embarassed now. Going to quietly read some more books, and experiment...
Thanks again!
Kevin Swanson
|
|
|
|
|
Hello, all database masters
I'm currently writing an application in Visual Basic.NET, and I would need to be able to update an ID_STATUS field for an Order table, using a combo box that would take it's value list from a Status table. I have been able to fix that in Access using the multi-columned comboboxes provided there, however I have no clue on how to do this in VB.NET. The Status table has only two columns, ID_STATUS (primary key, relates to "Order"'s ID_STATUS), and STATUS (which is the description for the specific ID). The IDs are numeric, that's how they are stored in the Status and Order tables, and I would like a use to be able to choose the Status from the combo box using the status' Text.
Would anyone have a clue on how to do this?
Matt.
|
|
|
|
|
Hi
I am using VB.net/ASP.NET and SQL Server 2000 for a web application.
I have AccountNo as identity column. I use some dummy records for testing. But, every time I do a Delete from table and Insert Dummy rows, the identity Number of AccountNo starts for (Max(AccountNo)+1) of previously inserted rows. This throws of the AccountNo associated with my Dummy rows and actual table table rows.
How can I reset the IDENTITY to start from 1 without re-creating the table?
Please advice. Thanks in advance.
Pankaj
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
Do a "Truncate Table MyTableName". That will delete all of the records from the table, and resets the seed for the identity column. Note that this will only work if you don't have any tables with foreign keys pointing at your table (you would have to drop the foreign key contraints before doing the truncate).
Hope this helps.
Andy
|
|
|
|
|
Thanks Andy...
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
I'm trying to get a view that contains a sub-query to work in a special way. Right now I'm getting the error message
ORA-01427: single-row subquery returns more than one row when I query the view.
Essentially what I want the subquery to return, is a single string of comma separated values of the multiple rows that exist. Is this possible?
So a table with 3 rows in it and the column that I'm querying has as data
ROWA
ROWB
ROWC
would return the string "ROWA,ROWB,ROWC".
Thanks.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
After some thought, I realized that what I needed was a 'grouping function'. So I wrote my own function that returns a single string of the comma separated data. Works like a charm.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
I have the following query where table1 located on Server1 and rest tables located on different server2.
How do I connect to server2 in order to perform this query?
INSERT Table1
select t.id,sum(p.paymenttotal)
from transactions t,payments p
where t.transactionnum=p.transactionnum
group by t.id
|
|
|
|
|
You have to use the full name of the object if you have objects on different servers. e.g.:
Server1.DatabaseName.dbo.Table1
or
Server2.DatabaseName.dbo.transactions
or
Server2.DatabaseName.dbo.payments
dbo=Database Owner
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I have a query that populates a datagrid. My question is how can i take the results in the datagrid and use them as parameters in an insert query. Essentially I am taking data out of one table and putting it back into another one.
|
|
|
|
|
Try to bind a DataSet into your DataGrid, therefore you can add/update/delete items in your DataSet through the DataGrid.
When you have finished, use a different SQLDataAdapter to insert/update another table.
Have a look at the example below:
ADO.NET: Update a Database from a DataSet
http://samples.gotdotnet.com/quickstart/aspplus/default.aspx?url=%2fquickstart%2fhowto%2fdoc%2fadoplus%2fupdatedatafromdb.aspx
|
|
|
|
|
I'm using this code to execute an stored procedure:
VARIANT vRecords;
_RecordsetPtr m_pRecordsetPtr;
irecordsaffected=0;
try{
vRecords.vt=VT_I4;
m_pRecordsetPtr=pQDef->Execute(&vRecords, NULL, adCmdText);
irecordsaffected= vRecords.iVal;
}
when I do it for "Insert" statements, the vRecords variable gets the correct value, but I get always 0 if I try an "Update" statement, any idea?.
thanks in advance.
|
|
|
|
|
The problem was that I was using allways the same _CommandPtr (pQDef), and never delete the Params used, this is why I got always 0 records when Update
and 1 record on the first Insert.
now works fine.
David.
|
|
|
|
|
Hi, Currently i need to display information from 1 table. This is the information of the table which contain 3 fields
Logdate ProjCode Hrs
2003-09-11 SBS 3
2003-09-11 asd 9
2003-09-11 asd 9
2003-09-12 dsa 6
2003-09-13 zxc 5
2003-09-13 cxz 2
When i add a new field using
Quey: Select *, (select Sum(Hrs) from Table a where a.Logdate = b.Logdate Group by Logdate)from Table b
Result:
Logdate ProjCode Hrs Sum
2003-09-11 SBS 3 22
2003-09-11 asd 9 22
2003-09-11 asd 9 22
2003-09-12 dsa 6 6
2003-09-13 zxc 5 7
2003-09-13 cxz 2 7
how do i edit my query statement where i can get
Result:
Logdate ProjCode Hrs Sum
2003-09-11 SBS 3 22
2003-09-11 asd 9 null
2003-09-11 asd 9 null
2003-09-12 dsa 6 6
2003-09-13 zxc 5 7
2003-09-13 cxz 2 null
as only display 1 sum in 1 logdate where the rest is null.
|
|
|
|
|
If you are using SQL-Server then one way would be:
create procedure RepProjHours<br />
as begin<br />
set nocount on<br />
<br />
--Create temporary table with line-number.<br />
create table #temp1 (<br />
LineNo integer identity not null,<br />
LogDate datetime null,<br />
ProjCode varchar(3) null,<br />
Hrs decimal(6,2) null<br />
)<br />
insert into #temp1<br />
select LogDate, ProjCode, Hrs<br />
from MyTimesheetTable<br />
order by LogDate, ProjCode, Hrs desc --Nice ordering?<br />
<br />
--Find the first timesheet entry for each day.<br />
create table #temp2 (<br />
LogDate datetime null,<br />
MinLineNo integer not null,<br />
TotalHrs decimal(6,2) not null<br />
)<br />
insert into #temp2<br />
select LogDate, Min(LineNo), Sum(Hrs)<br />
from #temp1<br />
group by LogDate<br />
<br />
--Join everything together (with subtotal on first line for each date).<br />
select T1.LogDate, T1.ProjCode, T1.Hrs, T2.TotalHrs<br />
from #temp1 T1<br />
left outer join #temp2 T2<br />
on T2.LogDate = T1.LogDate<br />
and T2.MinLineNo = T1.LineNo<br />
order by T1.LineNo<br />
end
Hope this helps. There are several other ways of achieving the same effect).
Andy
|
|
|
|
|
ALTER TABLE tt ALTER COLUMN [cc] [int] identity [(2, 1)] not null
and it failed with below error:
Incorrect syntax near the keyword 'IDENTITY'.
How can I make it work ?
Thanks
|
|
|
|
|
Remove the square brackets around the parentheses after 'IDENTITY'. Then it should work.
|
|
|
|
|
I Removed the square brackets around the parentheses after 'IDENTITY'.
ALTER TABLE tt ALTER COLUMN [cc] [int] identity (2, 1) not null
but it still failed with below error :
Incorrect syntax near the keyword 'IDENTITY'.
why?~
|
|
|
|
|
I have a query that fills a datagrid with a order number, order qty, and shipped qty. After I click a button to fill the grid I want the code to be able to highlight any rows where the order qty isn't equal to the shipped qty.
|
|
|
|
|
Hi,
Environment - VB.NET, ASP.NET, SQL Server 2000.
In a SQL User-Defined Function, I am selecting a column which returns multiple rows. I need to construct one single string out of those returned values. To do that, I am using CUROSR.
Now, CURSOR is expensive operation. If there are 1000 users at a time, it will consume lot of resources.
Is there a way, I can construct this String without using CURSORs??
Please advice. Thanks
Pankaj
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
I don't believe there is.
Generally, rolling up data is an operation best performed on the client in procedural code.
You can reduce the cost of your cursors by ensuring that you use a LOCAL FAST_FORWARD STATIC cursor.
|
|
|
|