[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1179
  • Last Modified:

problem sum query mysql vb net

When I test the query MySQL Administrator get all ok several records, but when I run the same query in the coda VB net applications get only one record.

why SUM operator in vb net returns only one record?

col2 is a different number and I need to sum ​​up all records with the same col2


query="slect sum (col1) as col1, col2, col3,col4,col5,myt1.scol1 from mytable join myt1 on myt1.id=mytable.idd where myt1.scol1='d1dd1d' group by col2, col3"

MySQL Server 5.1.11
MySQL Data 6.0.4
VB net 2008
0
imediagroup
Asked:
imediagroup
  • 2
  • 2
1 Solution
 
PortletPaulCommented:
The problem isn't you query most probably.

Try this query in MySQL Admin:
SELECT
      SUM(col1) AS col1
    , col2
    , col3
    , col4
    , col5
    , myt1.scol1
FROM mytable
      JOIN myt1
                  ON myt1.id = mytable.idd
WHERE myt1.scol1 = 'd1dd1d'
GROUP BY
      col2
    , col3
    , col4
    , col5
    , myt1.scol1

Open in new window

Is this the right result?

{+ edit} it is only slightly different to the existing query
0
 
ste5anSenior DeveloperCommented:
col2 is a different number and I need to sum ​​up all records with the same col2
E.g.

SELECT  SUM(col1) AS col1 ,
        col2
FROM    mytable
        JOIN myt1 ON myt1.id = mytable.idd
WHERE   myt1.scol1 = 'd1dd1d'
GROUP BY col2;

Open in new window

0
 
käµfm³d 👽Commented:
I agree with PortletPaul:  I've always understood GROUP BYs to require the same columns in the GROUP BY clause that you are retrieving in the SELECT--except for the aggregate column(s) [ i.e. the one you are SUMing].
0
 
PortletPaulCommented:
MySQL has a "group by extension" see: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

This allows fields that are not aggregations to be included in the select clause but not listed in the group by clause.

However, it "approximates" what you will see in those columns, my emphasis added:
useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group,
In short, this extension is bogus, and untrustworthy.

The reason I wanted the full query run (with the correct group by clause) was to ascertain if this produced better results. After that it is a matter of reducing the number of fields in the group by (and the select list) until the right level of summation is reached.


---
btw: some will advise that this MySQL extension be turned off
o disable the MySQL GROUP BY extension, enable the ONLY_FULL_GROUP_BY SQL mode. This enables standard SQL behavior: Columns not named in the GROUP BY clause cannot be used in the select list or HAVING clause unless enclosed in an aggregate function.
0
 
käµfm³d 👽Commented:
Good to know  = )
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now