Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-15
3
Medium Priority
?
359 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
This article covers the basics of data encryption, what it is, how it works, and why it's important. If you've ever wondered what goes on when you "encrypt" data, you can look here to build a good foundation for your personal learning.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

636 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