|
in query u put case statement 1,2,3 like that but problem is reference number should chage every time wat to do ... then how to retrieve the data..
|
|
|
|
|
Read the section on 'Concatenating values when the number of items is not known'.
|
|
|
|
|
|
plz gothrough it and plz understand my concept and solve it.. bcoz u not properly understand my output
|
|
|
|
|
select t1.empname,t1.referenceno,t2.salary
from table1 t1 join table2 t2
on t1.referenceno=t2.refereceno
|
|
|
|
|
its show answer like below
-----------------------------------|
empname | reference | salary
-----------------------------------|
girijesh | 111 | jan-10000
-----------------------------------|
girijesh | 111 | feb-20000
-----------------------------------|
girijesh | 111 | mar-30000
-----------------------------------|
ajay | 121 | jan-10000
-----------------------------------|
ajay | 121 feb-20000
-----------------------------------|
this wat if i executed ur query i got but i want reslut like below see ur ouput and wat am accepted output
so now result like below
-----------------------------------------------------|
empname | reference | salary
-----------------------------------------------------|
girijesh | 111 | jan-10000,feb-20000,mar-30000
-----------------------------------------------------|
ajay | 121 | jan-10000,feb-20000
-----------------------------------------------------|
i want query to display like above result
|
|
|
|
|
Use sort by or group by with the query to get the result.
|
|
|
|
|
I have a table Id , b1
Id B1 espectedresult
1 5 5
2 19 24
3 14 38
4 41 79
5 14 93
6 41 134
I want the sum b1 at every Id
can some one help?
|
|
|
|
|
select sum(b1),id from tablename group by id
|
|
|
|
|
It looks like what you want is a running total. I would have to do a search and do not have the time right now.
|
|
|
|
|
Assuming MSSQL < 2012, something like this should work:
SELECT
ID,
B1,
(SELECT Sum(B1) FROM TheTable As T2 WHERE T2.ID <= T1.ID) As Actual
FROM
TheTable As T1
ORDER BY
ID
http://www.sqlfiddle.com/#!3/59751/2[^]
For MSSQL 2012:
SELECT
ID,
B1,
Sum(B1) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As Actual
FROM
TheTable
ORDER BY
ID
http://www.sqlfiddle.com/#!6/59751/1[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
i want to know whats benefit use of index on view instead of table
thanks for any help
|
|
|
|
|
Read this[^].
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
hi to all
i have a table that product ID is key and clustered .
i create this view on my table if i not use of cluster index on view whats happen?
CREATE VIEW View3 WITH SCHEMABINDING AS
SELECT ProductID, SUM(UnitPrice*(1.00-UnitPriceDiscount)) AS Price,
COUNT_BIG(*) AS Count, SUM(OrderQty) AS Units
FROM Sales.SalesOrderDetail
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)
note to this point that Product ID also Cluster index in base table
thanks for any help
|
|
|
|
|
|
|
Hi
Using Microsoft.ACE.OLEDB.12.0 to access an MS Access 2007 database from a VB.net (2010) project.
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
WHERE tStk.[difference] <> 0
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTot
I only want rows where the sum of the item qty is Different in the two tables
The line:
WHERE tStk.[difference] <> 0 does not work. If I run the query from MS Access, it is asking for a value for the parameter tStk.[difference]
I also tried
WHERE sum(tStk.Qty) <> tHST.Qty but aggregate functions are not allowed in a WHERE clause.
|
|
|
|
|
Try taking your existing query and make it a sub-query.
Some thing like:
Select * from ("put your query here") XX
where XX.tStk.Qty <> tHST.Qty
Remember to remove your "Where" clause from your inner query.
|
|
|
|
|
Thanks for your reply David. Not sure if I understood correctly, but kept getting 'syntax error'
|
|
|
|
|
|
Awesome! Thanks Jörgen
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTot
HAVING sum(tStk.Qty) <>tHst.HstTot
I see that:
Aggregate functions can be used as expressions only in the following:
The select list of a SELECT statement (either a subquery or an outer query).
A COMPUTE or COMPUTE BY clause.
A HAVING clause.
|
|
|
|
|
whate is the different between two phase commit in SQL server and Oracl ???? :
modified 31-Mar-13 13:37pm.
|
|
|
|
|
|
My DBA is telling me that i should always insert into Views and never into tables. He indicates that if I do "inserts" into "tables" I am doing it "wrong" but didnt explain why. Can someone explain why he would choose inserting into Views or table inserts? Normally i would just insert into tables and use the Views as a pre-defined query.
Using MSSQL 2008R2.
Thanks
Eric
|
|
|
|
|
You may have misunderstodd, or he's wrong.
I've never heard of inserting into views, a view is essentially readonly, you have no idea how the data feeding it is structured -- one of the benefits of views. Then again databases have advanced quite a bit in the last twenty years and I may be behind the times. It may be that some versions of some databases support it in some cases, but I would never do it.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
