query /sql by group and variable

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
luca2009Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
tcs224694Commented:
Try this...
select sum(PROV!13),PROVA12 from DAT1 group by PROV12

Open in new window

0
 
louislietaerCommented:
I have been carefully reading your question. I don't undestand rset notion ? Is it result set ?
Wich database context ?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

0
 
luca2009Author Commented:

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
 
tcs224694Commented:
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
 
tcs224694Commented:
If u want the where clause use the one that u did for access...
0
 
luca2009Author Commented:
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
 
tcs224694Commented:
Did it throw any error or simple no records...
0
 
luca2009Author Commented:
tcs224694:
simple no records...NO ERROR!
0
 
SharathData EngineerCommented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.