reflecto compute sum in list view

how do i reflect this

ssql = "  SELECT disposicion,moneda, SUM(MONTO) as monto,tipo  from disposiciones where  tipo Like '%" & txtsearch.Text & "%'  group by disposicion,tipo,moneda"

in my listview
cant report it
tsm
ErnestoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ErnestoAuthor Commented:
i create a field to alocate the sum but give me an error in this query
any advice?

update disposiciones set totalglob=(SELECT  SUM(MONTO) as monto  from disposiciones   group by disposicion,tipo,moneda) where disposicion=disposicion and tipo=tipo and moneda=moneda

tsm
edo
ErnestoAuthor Commented:
the error were

'La subconsulta ha devuelto más de un valor, lo que no es correcto cuando va a continuación de =, !=, <, <=, >, >= o cuando se utiliza como expresión.'

' the subquery returns more than one value that is correct when is preceded by  =, !=, <, <=, >, >= or where is used as expresion'

some like that
tsm
ErnestoAuthor Commented:
please you guys
any advice?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ErnestoAuthor Commented:
or
insert into disposiciones(totalglob) values (select SUM(MONTO)  from disposiciones group by disposicion,tipo,moneda)
Bob LearnedCommented:
Is that code inside a method?  If so, then it is pretty difficult to get that code with reflection.  it would be easier if you had a field or property.
Vadim RappCommented:
I don't quite understand the problem. You have a listview control, then what? how is it related to the SQL SELECT statement from your first post? then what about your UPDATE statement, how do you use it?
ErnestoAuthor Commented:
ok forget the listview

how do i achive that


insert into disposiciones(totalglob) values (select SUM(MONTO)  from disposiciones group by disposicion,tipo,moneda)

tsm you expert
Bob LearnedCommented:
Are you asking how to take that query, and insert the items into a ListView?

Is this an ASP.NET web site, or a Windows Forms application?
ErnestoAuthor Commented:
only the query
please
tsm
PortletPaulEE Topic AdvisorCommented:
So far you have asked for queries to do a select, an update and an insert.

1
SELECT disposicion,moneda, SUM(MONTO) as monto,tipo  from disposiciones where  tipo Like '%" & txtsearch.Text & "%'  group by disposicion,tipo,moneda"

2
update disposiciones set totalglob=(SELECT  SUM(MONTO) as monto  from disposiciones   group by disposicion,tipo,moneda) where disposicion=disposicion and tipo=tipo and moneda=moneda

3
insert into disposiciones(totalglob) values (select SUM(MONTO)  from disposiciones group by disposicion,tipo,moneda)

Open in new window


Only query 1 looks reasonable. The other 2 queries appear to be attempting the impossible.

However, it also seems that query 2 & 3 are attempts to put SUM(MONTO) into the table disposiciones

BUT, I think you are trying to do that so you can see the result in a list view.

I don't know anything about your listview but perhaps this query structure will allow it to be seen:
SELECT
      disposicion
    , moneda
    , MONTO
    , tipo
FROM (
      SELECT
            disposicion
          , moneda
          , SUM(MONTO) AS MONTO
          , tipo
      FROM disposiciones
      WHERE tipo LIKE '%" & txtsearch.Text & "%'
      GROUP BY
            disposicion
          , tipo
          , moneda
) AS D;

Open in new window


If you need more SQL assistance the very best way to resolve SQL questions is to provide some "sample data" and the "expected result".
ErnestoAuthor Commented:
ok i want this

ABB MEXICO, S.A. DE C.V.            30768.54      PENDIENTE
ABB MEXICO, S.A. DE C.V.      .      10801.68      PENDIENTE
ABB MEXICO, S.A. DE C.V.      .      15751.77      PENDIENTE
EMERSON PROCESS MANAGEMENT, SA DE CV      `      91605.25      F-DF147090
EMERSON PROCESS MANAGEMENT, SA DE CV      `      68765.55      F-DF147092

i want to sum all this amounts to a new field called  totalglob
ErnestoAuthor Commented:
all this items are in disposicion 1 i  want to do the sume of items where the disposicion were change,
i mean where is disposicion 2, 3 etc
tsm
Vadim RappCommented:
insert into disposiciones(totalglob)
select SUM(MONTO)  from disposiciones group by disposicion,tipo,moneda
ErnestoAuthor Commented:
vladi, almost
as update? dont want new records, only the sum in the existing ones
tsm
ErnestoAuthor Commented:
how to achive


update disposiciones set  disposiciones(totalglob)
select SUM(MONTO)  from disposiciones group by disposicion,tipo,moneda
Vadim RappCommented:
Actually, as I looked more closely at what you are trying to achieve, I think it's bad decision to begin with. Here's why.

As I understand, you have table disposiciones with columns  disposicion,tipo,moneda, MONTO, totalglob

And you want to put the total of MONTO aggregated by disposicion,tipo,moneda into each row. So:

Before:
disposicion,tipo,moneda, MONTO, totalglob
1                  1     1             5
1                  1     1             3
1                  1     2             11
1                  1     2             2

after:

disposicion,tipo,moneda, MONTO, totalglob
1                  1     1             5              8                       <---- 5+3
1                  1     1             3              8                       <---- 5+3
1                  1     2             11            13                      <---- 11+2
1                  1     2             2              13                      <---- 11+2


This is bad idea to have the same value in every row. The reasons are from the area of relational databases, but believe me, this is very bad decision and it will make things more complex that they need to be.

You will be much better off if you create separate table just for the totals:

disposicion,tipo,moneda, totalglob
1                  1     1             8
1                  1     2             13

Or, alternately, in the application that needs these totals, you specify that SUM(monto) in sql statement, so it's calculated whenever it's needed.

Or you can create a view:
create view disposicion_totals as
select sum(monto) as totalglob ,
disposicion,tipo,moneda
from disposiciones
group by disposicion,tipo,moneda

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ErnestoAuthor Commented:
dont care
how to achive
tsm

update disposiciones set  disposiciones(totalglob)
select SUM(MONTO)  from disposiciones group by disposicion,tipo,moneda
ErnestoAuthor Commented:
thats it!!!
the view
ErnestoAuthor Commented:
how to update this view  as needed?
ErnestoAuthor Commented:
is any way to  made totalglob as $0,000,000?
tsm vladim
Vadim RappCommented:
>how to update this view  as needed?
you don't update the view. Whenever you invoke the view, it will show the data based on what's currently in the table.
So if you open view and it shows total=5, then you add new line to the table and open view again, it will show new total.

>  is any way to  made totalglob as $0,000,000?
Formatting is usually specified in the client application that shows the data on the screen. Depends on which control you are using to show the data.
ErnestoAuthor Commented:
ok, tsm
thats it!!!
ErnestoAuthor Commented:
the last solution for currency were

  SELECT disposicion,moneda, '$ ' + convert(varchar(14), totalglob, 1) AS total,tipo FROM disposicion_totals

tsm all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.