Solved

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

Posted on 2011-02-15
3
350 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
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
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…

680 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