?
Solved

Can anyone tell me how to do equations with concat_goup query MYSQL

Posted on 2011-02-15
3
Medium Priority
?
354 Views
Last Modified: 2012-05-11
I have the follow query:

SELECT
ID,
sqldate,
sqlvalue,
CAST(GROUP_CONCAT(if(stat_number = 138, sqlvalue, NULL)) AS UNSIGNED) AS Total,
CAST(GROUP_CONCAT(if(stat_number = 144, sqlvalue, NULL)) AS UNSIGNED) AS Public,
FROM statval1
WHERE (DAYOFWEEK(sqldate) = 5)
GROUP BY sqldate
ORDER BY sqldate

for Table:
ID-   sql Date      - Stat_number - sqlvalue -
1 - 2002/12/5     -154           - 652
2- 2002/12/5      -138           - 547
3- 2002/12/5      -144           - 687
4- 2002/12/5      -195           - 687
5 - 2002/12/12   -138           - 652
6- 2002/12/12    -154           - 547
7- 2002/12/12    -195           - 687
8- 2002/12/12    -144           - 687

I need to be able to multiply by rows the `total ` * 100 divided by `Public` and the end result
to be entered into a new colum `result`. This is grouped by date.

The query result should look like this:
    sqldate          - total    -     public   -    result   -
 2002/12/5        - 547      -     687     -    79.62
 2002/12/12      - 652      -     687     -     94.90

The could be 20 fields with the same date but a stat number will only show up once
per date (no duplicate stat number on columns with the same date)

Can anyone complete my query to generate the result required?
0
Comment
Question by:DPP2011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 2000 total points
ID: 34902266
You can't refer to them as total and public in the same query. You could nest the query or retype the portions you have so far. Also, use 100.0 so that it automatically casts to a double.
SELECT
ID,
sqldate,
sqlvalue,
CAST(GROUP_CONCAT(if(stat_number = 138, sqlvalue, NULL)) AS UNSIGNED) AS Total,
CAST(GROUP_CONCAT(if(stat_number = 144, sqlvalue, NULL)) AS UNSIGNED) AS Public,
CAST(GROUP_CONCAT(if(stat_number = 138, sqlvalue, NULL)) AS UNSIGNED) * 100 /CAST(GROUP_CONCAT(if(stat_number = 144, sqlvalue, NULL)) AS UNSIGNED)  AS result,
FROM statval1
WHERE (DAYOFWEEK(sqldate) = 5)
GROUP BY sqldate
ORDER BY sqldate
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34902293
The other option (nesting) would look like this (be careful since Public is a keyword)
SELECT
ID,
sqldate,
sqlvalue,
Total,
Public,
Total*100.0/Public AS result
FROM
(
SELECT
ID,
sqldate,
sqlvalue,
CAST(GROUP_CONCAT(if(stat_number = 138, sqlvalue, NULL)) AS UNSIGNED) AS Total,
CAST(GROUP_CONCAT(if(stat_number = 144, sqlvalue, NULL)) AS UNSIGNED) AS Public,
FROM statval1
) tbl
WHERE (DAYOFWEEK(sqldate) = 5)
GROUP BY sqldate
ORDER BY sqldate
0
 

Author Closing Comment

by:DPP2011
ID: 34902441
Thank you,  Solved the problem.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

When we purchase storage, we typically are advertised storage of 500GB, 1TB, 2TB and so on. However, when you actually install it into your computer, your 500GB HDD will actually show up as 465GB. Why? It has to do with the way people and computers…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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