Solved

arrange my sql data

Posted on 2013-01-25
4
257 Views
Last Modified: 2014-05-19
I am creating a report to show the break down of people symptoms. The symptom field is comma separated.  I have tried quit a few things with  sql but I can't get the data the way I want it.
 
                             Table A
symptom                                  ID
Cold,Flu,Pink Eye                       18
Cold                                           22
Flu,Bloody Nose                        23
Flu, Pink Eye, Bloody Nose        24

What I want to get is the count of how many people have colds and their ID, how many people have Flu and their ID  like
Cold 2 18,22
Flu 3 18,23,24
Is their a way of doing this?
0
Comment
Question by:spectrumcare
[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
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 38821767
Your main problem is the way your data is stored - it is hard to generate reports the way it was done.  It should have been stored liked this:

symptom                                  ID
Cold                                         18
Flu                                            18
Pink Eye                                    18
Cold                                           22
Flu                                        23    
Bloody Nose                        23
Flu                                        24
Pink Eye                               24
Bloody Nose                        24

The above is the standard way to store data with many-to-many relationship.  If you had it done that way you could have done this:

select symptom, count(id), group_concat(id) from table_name group by symptom;

Rather than developing a complicated workaround I suggest you focus on fixing your data structure - it will solve many other things along the way.
0
 

Author Comment

by:spectrumcare
ID: 38827022
I do agree but this data is being stored is coming from a text area box. The user types in the symptom and they can edit this when they want.  To do a database change for 1 report is not worth changing multiple pages for it. I did however come up with a way of doing it on the week end.  It is probably not the best way of doing it but it is a way.
I loop my query getting the symptoms splitting the sting and putting the values into an array. I then loop the array querying the original query to find the symptom that matches. The record count of the query gives me how many people have the symptom and I have their ID's to get their name.
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 38829625
So what happens if the user forgets to separate symptoms with a comma (I assume that's the delimiter you use)?  Your report might contain something like:

Pink Eye     5     10,11,12,13,14
Pink Eye Bloody Nose     1      15
Bloody Nose      3      16,17,18

If the client can live with that, then fine.  But if I foresee more complicated requirements later on I would recommend revisiting the database design.
0
 
LVL 3

Expert Comment

by:stevejacob68
ID: 40076806
Hi,
Try this statement:
Select * from table_name where symptom=”cold” or symptom=”flu” or symptom=”pink” or symptom=”eye”
And write other statement like this to generate other output
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

729 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