Database

ritesh.gupta555525-Jun-13 2:55

ritesh.gupta5555

25-Jun-13 2:55

Use sort by or group by with the query to get the result.

SQL QUERY

Dobrobit2-Apr-13 8:41

Dobrobit

2-Apr-13 8:41

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?

David Mujica2-Apr-13 9:12

David Mujica

2-Apr-13 9:12

select sum(b1),id from tablename group by id

Corporal Agarn2-Apr-13 9:17

Corporal Agarn

2-Apr-13 9:17

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.

when use of index on view instead of table

Richard Deeming2-Apr-13 10:40

Richard Deeming

2-Apr-13 10:40

Assuming MSSQL < 2012, something like this should work:

SQL

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:

SQL

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

mhd.sbt2-Apr-13 7:01

mhd.sbt

2-Apr-13 7:01

i want to know whats benefit use of index on view instead of table

thanks for any help

Re: when use of index on view instead of table

R. Giskard Reventlov2-Apr-13 7:11

R. Giskard Reventlov

2-Apr-13 7:11

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

benefit of cluster index on view in sql 2008 r2

mhd.sbt2-Apr-13 1:45

mhd.sbt

2-Apr-13 1:45

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?

SQL

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

Re: benefit of cluster index on view in sql 2008 r2

Simon_Whale2-Apr-13 1:56

Simon_Whale

2-Apr-13 1:56

Have a read of this

http://blog.sqlauthority.com/2010/10/12/sql-server-indexed-view-always-use-index-on-index/[^]

and this is a detailed article on how the clustered index works
https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/[^]

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: benefit of cluster index on view in sql 2008 r2

mhd.sbt2-Apr-13 5:16

mhd.sbt

2-Apr-13 5:16

thank you for reply

Difference in Sum(column) between two tables

Richard.Berry1001-Apr-13 3:45

Richard.Berry100

1-Apr-13 3:45

Hi
Using Microsoft.ACE.OLEDB.12.0 to access an MS Access 2007 database from a VB.net (2010) project.

SQL

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:

SQL

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

SQL

WHERE sum(tStk.Qty) <> tHST.Qty

but aggregate functions are not allowed in a WHERE clause.

David Mujica1-Apr-13 4:12

David Mujica

1-Apr-13 4:12

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.

Richard.Berry1004-Apr-13 8:05

Richard.Berry100

4-Apr-13 8:05

Thanks for your reply David. Not sure if I understood correctly, but kept getting 'syntax error'

Jörgen Andersson1-Apr-13 8:21

Jörgen Andersson

1-Apr-13 8:21

Maybe HAVING sum(tStk.Qty) <> tHST.Qty[^] would work better

"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull

http://en.wikipedia.org/wiki/2PC[^]

Richard.Berry1004-Apr-13 7:34

Richard.Berry100

4-Apr-13 7:34

Awesome! Thanks Jörgen

SQL

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.

2pc in SQL server

Soiraian31-Mar-13 6:21

Soiraian

31-Mar-13 6:21

whate is the different between two phase commit in SQL server and Oracl ???? Confused | :confused:

modified 31-Mar-13 13:37pm.

Re: 2pc in SQL server

RedDk31-Mar-13 7:44

RedDk

31-Mar-13 7:44

Views vs Tables

Eric Whitmore29-Mar-13 4:50

Eric Whitmore

29-Mar-13 4:50

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

PIEBALDconsult29-Mar-13 5:37

PIEBALDconsult

29-Mar-13 5:37

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.

Eric Whitmore29-Mar-13 5:47

Eric Whitmore

29-Mar-13 5:47

I had the same thoughts. You "can" do it in MS SQL 2008R2 but i never have... It seems to me to be a really bad practice as the view can (and does in my case) span multiple containers & servers.

Eric

PIEBALDconsult29-Mar-13 6:21

PIEBALDconsult

29-Mar-13 6:21

Eric Whitmore wrote:
a really bad practice

Yes, it is. It's got to be less efficient as the engine has to figure out how to do it and in a great many cases it simply can't. Plus it's not database agnostic, so why go to the trouble when inserting to tables works everywhere?

"Simplify. Simplify." -- Thoreau

GuyThiebaut30-Mar-13 5:29

GuyThiebaut

30-Mar-13 5:29

I recently tested updates on a mock view, in SQL Server 2008, which contained a join between two tables.
I did this because where I work the majority of what our users see is through views and they occasionally need to update what they see.

I read up on it beforehand and all the documentation pointed to this being possible as long as it was unambiguous as to which row in which table was being updated.

The update did work - I then tried to break it and succeeded by using an aggregate function in the view so that there was ambiguity in what would be updated.

It can be useful to insert or update a view, since if one is passing a view into a control then it is easier to perform an update, on the source of that control, than to have to attach lots of meta data documenting which tables to update.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

PIEBALDconsult30-Mar-13 5:51

PIEBALDconsult

30-Mar-13 5:51

GuyThiebaut wrote:
passing a view into a control

But that's not a good idea. It doesn't lead to a properly layered application.

GuyThiebaut wrote:
aggregate function

Or a UNION or a CASE , probably if there's CAST or CONVERT ... I suppose it requires that there be a primary key as well.

GuyThiebaut30-Mar-13 5:57

GuyThiebaut

30-Mar-13 5:57

"But that's not a good idea. It doesn't lead to a properly layered application." - I don't understand why using a view in a control would do this, could you let me know why?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens