Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

query /sql by group and variable

Posted on 2009-04-25
11
Medium Priority
?
310 Views
Last Modified: 2012-05-06
I need a SQL string to group by rset based a variable..
Attached sheet with structure of my table named DATI.
Admit:
var1=4500 is refred the filed (PROVA2)
var2=07/04/2009 is refred the filed (PROVA9)
var3=BUSINESS is refred the filed (PROVA12)
var4=50163743 is refred the filed (PROVA119)

How to Group By the rset based the for variable and return the summ by PROV13
In this case with the for param the sum is 287.293,81
DATI.zip
0
Comment
Question by:luca2009
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 2

Expert Comment

by:tcs224694
ID: 24231758
Try this...
select sum(PROV!13),PROVA12 from DAT1 group by PROV12

Open in new window

0
 
LVL 5

Expert Comment

by:louislietaer
ID: 24231780
I have been carefully reading your question. I don't undestand rset notion ? Is it result set ?
Wich database context ?
0
 

Author Comment

by:luca2009
ID: 24231877
hi.... rset =recordset
in effect i want afetr query sql this ressult:
4500 07/04/2009 BUSINESS 287.293,81 50163743

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:luca2009
ID: 24231928

In Acess query editor have tested the sql:

SELECT sum(PROVA13) AS TOTALI, PROVA2, PROVA9, PROVA12, PROVA19
FROM DATI
WHERE PROVA2='4500' AND PROVA9=07/07/2009 AND PROVA12='BUSINESS' AND PROVA19='50163743'
GROUP BY PROVA2, PROVA9, PROVA12, PROVA19;

but the result have all rset blank! i see only the name of fileds
0
 
LVL 2

Expert Comment

by:tcs224694
ID: 24231940
Hmmm,Try this and let me know the results.
SELECT PROVA02,PROVA09,PROVA12,SUM(PROVA13),PROVA19 FROM DAT1 GROUP BY PROVA12

Open in new window

0
 
LVL 2

Expert Comment

by:tcs224694
ID: 24231947
If u want the where clause use the one that u did for access...
0
 

Author Comment

by:luca2009
ID: 24231970
But into query i need to insert a clausole WHERE to set the value of variable when the query filter, see here:

In Acess query editor have tested the sql:

SELECT sum(PROVA13) AS TOTALI, PROVA2, PROVA9, PROVA12, PROVA19
FROM DATI
WHERE PROVA2='4500' AND PROVA9=07/07/2009 AND PROVA12='BUSINESS' AND PROVA19='50163743'
GROUP BY PROVA2, PROVA9, PROVA12, PROVA19;

but the result have all rset blank! i see only the name of fileds
0
 
LVL 2

Expert Comment

by:tcs224694
ID: 24231982
Did it throw any error or simple no records...
0
 

Author Comment

by:luca2009
ID: 24232683
tcs224694:
simple no records...NO ERROR!
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24233293

You don't need GROUP BY. You want to filter the records and then find the sum. You need WHERE clause. After all filters sum of PROVA13.
What is your SQL version? If MS Access, try this.
SELECT SUM(PROVA13) AS TOTALI
 FROM DATI
WHERE [PROVA2] = 4500
  AND [PROVA9] = #04/07/2009#
  AND [PROVA12] ='BUSINESS'
  AND [PROVA19] = 50163743
If is SQL Server, then try this.
SELECT SUM(PROVA13) AS TOTALI
 FROM DATI
WHERE [PROVA2] = 4500
  AND [PROVA9] = '04/07/2009'
  AND [PROVA12] ='BUSINESS'
  AND [PROVA19] = 50163743
 

 
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 24235465
If you get any type mismatch error, you can use quotes. Also if you want to display the filtered columns in the SELECT clause,
you can include those by adding GROUP by on the same columns.
SELECT [PROVA2], 
       [PROVA9], 
       [PROVA12], 
       [PROVA19], 
       SUM(PROVA13) AS TOTALI
  FROM DATI
 WHERE [PROVA2] = '4500' 
   AND [PROVA9] = #04/07/2009# 
   AND [PROVA12] ='BUSINESS' 
   AND [PROVA19] = '50163743'
 GROUP BY [PROVA2], [PROVA9], [PROVA12], [PROVA19] ;

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question