MySQL Query

Dear Expert,

I want to get result like attachment. How can I query like this?]

Regards,
Rada
query.xlsx
arkam chouAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
select a.dataid,b.brandname,c.spendname, a. Numberofspend from dataentry a  join brandname b on a.brandid=b.brandid join spendname  c on a.spendid=c.spendid
0
arnoldCommented:
Oh the first gives you the top, you want what is called in MS a pivot table.
0
arkam chouAuthor Commented:
the result is like that:
     1. group BrandName
     2. put spendtype by each column


the result
BrandName   TV        Radio   Magazine
Bank                111      444      777
Beer                 222     555       888
Drug                 333    666        999
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

arnoldCommented:
select b.brandname,
(If (c.spendname='TV', a. Numberofspend,0)) as TV,
(If (c.spendname='Radio', a.Numberofspend, 0)) as Radio,
(If (c.spendname='Magazine', a.numberofspend,0)) as Magazine

from dataentry a  
join brandname b on a.brandid=b.brandid
join spendname  c on a.spendid=c.spendid
group by b.brandname

Try tge above.

There are many MySQL pivot examples.
0
arkam chouAuthor Commented:
Hello,

When I try it, it show message not correct like below;

 brandname       TV       Radio       Magazine       
Bank              111       0               0
Beer              0       222               0
Drug              0       0            333
0
arnoldCommented:
Oh, you need to add the c.spendname to the group by directive.
Group by b.brandname, c.spendname
0
arnoldCommented:
I'll recheck the query later on today, I think the group by is might not be enough.
0
arnoldCommented:
Never mind, kept going back and forth, messing myself up.

select b.brandname,
(If (c.spendname='TV') then a. Numberofspend) as TV,
(If (c.spendname='Radio') then a.Numberofspend) as Radio,
(If (c.spendname='Magazine') then  a.numberofspend) as Magazine

from dataentry a  
join brandname b on a.brandid=b.brandid
join spendname  c on a.spendid=c.spendid
group by b.brandname
0
arkam chouAuthor Commented:
Hello,

I got message like attachment.
0
arkam chouAuthor Commented:
0
arnoldCommented:
Add spaces before and after the parenthesis signs.
0
arnoldCommented:
Another possibility use select a.Numberofspend instead of a.numberofspend in the example above.

If neither works, do not ha access right now to test the query directly, will have later in today.
0
arkam chouAuthor Commented:
it still not yet work.
5-24-2015-1-56-01-PM.jpg
0
arnoldCommented:
Does it error out without the select within the if query?
0
arkam chouAuthor Commented:
it error when put "Then"
0
arnoldCommented:
select b.brandname,
(If (c.spendname='TV' ) then a. Numberofspend end if ) as TV,
(If (c.spendname='Radio' ) then a.Numberofspend end if ) as Radio,
(If (c.spendname='Magazine' ) then a.numberofspend end if ) as Magazine

from dataentry a  
join brandname b on a.brandid=b.brandid
join spendname  c on a.spendid=c.spendid
group by b.brandname
0
arkam chouAuthor Commented:
it show error message like this
5-24-2015-2-29-23-PM.jpg
0
arnoldCommented:
select brandname,
(If (spendname='TV' ) then Numberofspend end if ) as TV,
(If (spendname='Radio' ) then Numberofspend end if ) as Radio,
(If (spendname='Magazine' ) then numberofspend end if ) as Magazine
From (select b.brandname,c.spendname,a.numberofspend
from dataentry a  
join brandname b on a.brandid=b.brandid
join spendname  c on a.spendid=c.spendid
)
group by brandname
0
arkam chouAuthor Commented:
still got error
5-24-2015-2-46-45-PM.jpg
0
arnoldCommented:
Add a semi-colon (;) after end if
In any of the examples.
0
arkam chouAuthor Commented:
still got error around "Then"
5-24-2015-3-30-08-PM.jpg
0
arnoldCommented:
select b.brandname,
(MAX(If (c.spendname='TV', a. Numberofspend,0))) as TV,
(MAX(If (c.spendname='Radio', a.Numberofspend, 0))) as Radio,
(MAX(If (c.spendname='Magazine', a.numberofspend,0))) as Magazine

from dataentry a  
join brandname b on a.brandid=b.brandid
join spendname  c on a.spendid=c.spendid
group by b.brandname,c.spendname

returns the data, you want
0
arkam chouAuthor Commented:
Good,

Now got result as in attachment. how can I sort it like my first attachment?
5-24-2015-7-35-55-PM.jpg
0
arkam chouAuthor Commented:
how can I group name and sum number like this:

BrandName      TV            Radio      Magazine
Bank              111            444          777
Beer               222       555              888
Drug               333       666              999
0
arnoldCommented:
I do not understand, how is it coming out?
The order in which you place the if statements will deal with the order of the spend type item order.

You can try using order by, but
0
arkam chouAuthor Commented:
can I got result like attachment?
query.xlsx
0
arkam chouAuthor Commented:
Hello Arnold,

I just ask if any update.
0
arnoldCommented:
What are the results that you do get?
Beer has 222 radios not TVs,

Change your data in dataentry to match the results you want.
0
arkam chouAuthor Commented:
I am very sorry Arnold that make you headache. I put data in wrong order. please find attachment
Pivot.xlsx
0
arnoldCommented:
Run the query, what is the output that you do get?

You can try adding the order by, but you would need to add the column you want to order by to the group by column
0
arkam chouAuthor Commented:
the result after query is like in attachment but it not all same name in only one name and don't auto sum.
result.jpg
0
arnoldCommented:
You can not alter the output because your input is not in that orientation.
Not sure which query you are running. Your DataEntry does not have 111,222,333 for the TVs only.

select b.brandname,
(MAX(If (c.spendname='TV', a. Numberofspend,0))) as TV,
(MAX(If (c.spendname='Radio', a.Numberofspend, 0))) as Radio,
(MAX(If (c.spendname='Magazine', a.numberofspend,0))) as Magazine

from dataentry a  
join brandname b on a.brandid=b.brandid
join spendname  c on a.spendid=c.spendid
group by b.brandname,c.spendname

Open in new window


The above query will return a 3x3 data set that you want but not with the pattern you want.  The only way to achieve the pattern is to sell some Tvs while buying some beer branded radio until such time that you have 111 bank Tvs, 222 Beer Tvs and 333 Drug TVs.

I am not sure there is any query that I can run to reverse this trend.
0
arkam chouAuthor Commented:
Dear arnold,

I think I had made some wrong explain because of English is not my original. Please help to find attachment again.
Pivot.xlsx
0
arnoldCommented:
Are we dealing with a different set if data.
Sort by dataid

This question has for different queries within.

The examples deal with the query on MySQL, the data is then can be used in php to display it in the manner you want short of altering the information/location.

The first join include a.dataid and sort by a.dataid.

I believe you have all the variants of queries you need.

(max(if
Is the last that will return a 3x3 only output.
0
arkam chouAuthor Commented:
if it need php to help display like my "wanted result" in excel. so what should to do with php?
0
arnoldCommented:
The Max is now a sum as you are summing up the values

Your initial question is different and continues to evolve.

The pivoting is done through the group by and aggregation that max or sum will do.

Once you run the query, PHP has the response. You can display output the data to any format you want after.
I.e. To make the last column to show the value in $.

The output to appear as a table has to be done within php I.e, adding the th, tr,td html structure.


I think you have enough here to deal with the assembling the data in various options as well as pivoting the table..
0
arkam chouAuthor Commented:
I know you give me nearly complete answer but I need a little more on make by put brandname in one row and sum magazine, radio, tv where brandname is same. it look like this:

this is your query
ex:
                 magazine        radio            tv
Bank        0                       0                   1
Bank        0                       1                   0
Bank        1                       0                    1

and I want like below:
                  magazine        radio           tv
Bank         1                       1                   1
0
arnoldCommented:
Please note the last queries use MAX or sum depending on which you want.

You might be using one of the many versions I posted leading up.  Look at the last one I posted here two posts ago with (max(if) the if determines the value for the spend type,

Please post the query you are using when you got what you got.

What isthe result of
This will sum the various brand/spend entries
select b.brandname,
(Sum(If (c.spendname='TV', a. Numberofspend,0))) as TV,
(Sum(If (c.spendname='Radio', a.Numberofspend, 0))) as Radio,
(Sum(If (c.spendname='Magazine', a.numberofspend,0))) as Magazine

from dataentry a  
join brandname b on a.brandid=b.brandid
join spendname  c on a.spendid=c.spendid
group by b.brandname

Open in new window


The below will only output the max in a three by three display or at least should

select b.brandname,
(MAX(If (c.spendname='TV', a. Numberofspend,0))) as TV,
(MAX(If (c.spendname='Radio', a.Numberofspend, 0))) as Radio,
(MAX(If (c.spendname='Magazine', a.numberofspend,0))) as Magazine

from dataentry a  
join brandname b on a.brandid=b.brandid
join spendname  c on a.spendid=c.spendid
group by b.brandname

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arkam chouAuthor Commented:
yes! this one

it could perform by change from "group by b.brandname , c.spendname" to "group by b.brandname"
0
arnoldCommented:
Which sum or max?
The sum will add up the multiple brand/spendtype combinations, while the max, will return the entries based on individual Max.
0
arkam chouAuthor Commented:
the sum is right for me.  I am now testing with my real system that had extra one column "location"

brandname    location           tv            radio        magazin
Bank                city                   1              1               1
Bank                countryside    2              2               2
Bank                city                   1              2               3

I will test group by brandname and location

Bank               city               2           3           4
Bank               count...        2           2           2
0
arnoldCommented:
Add the location column to the select  as well as the to the group by brandname,location

Is the locationid that you need to join location d on a.locationid=d.locationid
0
arkam chouAuthor Commented:
yes! that right.
0
arnoldCommented:
So you modified the select adding d.locarionname
and adding the
Join location d on a.locationid=d.locationid

Group by a.brandname,d.location ame
0
arkam chouAuthor Commented:
I am very proud that was here meeting all of expert. I would like to say that I am very thank you so much for this many day on trying to give me what I want. I am very also embarrass too that ask question here by not learning much. I try to read book on html, css, mysql and php but I can only work on basic. even though I think it is also good for asking, answer and sharing knowledge and experience for improve knowledge of asking person and answer person.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.