slothnet
asked on
Access to mysql concat sum
I have a table in access with about 180k records. I have a bunch of sql statements that I tested and work perfectly on a smaller version of my data. With access & that amount of records, things are pretty slow, but a sql statement with a Dconcat function I even let operate overnight didn't get even close to 1/4 way through.
I assumed the Jet engine wont cut it for all those records, I transfered my data to mysql and added a primary key. In access I then "linked" the table to mysql via ODBC and still gives signs of taking forever and leaving it for a half hour.
As a last attempt I'm going to try native mysql functions and execute sql in mysql. My access function concatenates and extracts a total sum thats allready in the table to give this kind of result:
name class Tqty QTY name classWqty
joe A 20 10 joe a (20), b(30)
joe A 20 9
joe A 20 1
joe B 30 30
In access I made column Tqty because I was having trouble summing QTY in the original dconcat access function.
I'm new to mysql but found theres a concat & group_concat function in mysql that might do the trick, otherwise I imagine I'll have to do a user defined function unless theres a way to nest, them of which I don't know a thing about. Do you have anything that will work?
I assumed the Jet engine wont cut it for all those records, I transfered my data to mysql and added a primary key. In access I then "linked" the table to mysql via ODBC and still gives signs of taking forever and leaving it for a half hour.
As a last attempt I'm going to try native mysql functions and execute sql in mysql. My access function concatenates and extracts a total sum thats allready in the table to give this kind of result:
name class Tqty QTY name classWqty
joe A 20 10 joe a (20), b(30)
joe A 20 9
joe A 20 1
joe B 30 30
In access I made column Tqty because I was having trouble summing QTY in the original dconcat access function.
I'm new to mysql but found theres a concat & group_concat function in mysql that might do the trick, otherwise I imagine I'll have to do a user defined function unless theres a way to nest, them of which I don't know a thing about. Do you have anything that will work?
HainKurt
please post a sample data and what are you trying to get from this sample data...
also it is not a good idea to add a new column to keep the sums... you should be able to get that data with query...
select [name], sum([qty]) as sum_qty
where [name]='joe'
or you can group by to get all
select [name], sum([qty]) as sum_qty
group by [name]
order by [name]
select [name], sum([qty]) as sum_qty
where [name]='joe'
or you can group by to get all
select [name], sum([qty]) as sum_qty
group by [name]
order by [name]
It looks like you need to normalize your database. putting everything into one big table is usually a really bad idea, and is probably contributing to your speed problem. The wikipedia article on it is probably sufficient for what you are doing right now: http://en.wikipedia.org/wiki/First_normal_form
Furthermore, database engines weren't designed with creating strings of that size. group_concat for example only does 1024 characters. You will probably want to stream it out to a file as you calculate it, or to the web if that is where you are looking at the data. Are you using php with this? or a different language? Where do you want the output of this to end up?
Furthermore, database engines weren't designed with creating strings of that size. group_concat for example only does 1024 characters. You will probably want to stream it out to a file as you calculate it, or to the web if that is where you are looking at the data. Are you using php with this? or a different language? Where do you want the output of this to end up?
Also email the author of the dconcat function and ask if there are any performance issues with large datasets
https://www.experts-exchange.com/M_2428436.html
You can also try a Crosstab query to get the output you are looking for:
TRANSFORM First(YourTable.[class]) AS FirstOfclass
SELECT YourTable.[name]
FROM YourTable
GROUP BY YourTable.[name]
PIVOT YourTable.[Tqty];
;-)
JeffCoachman
https://www.experts-exchange.com/M_2428436.html
You can also try a Crosstab query to get the output you are looking for:
TRANSFORM First(YourTable.[class]) AS FirstOfclass
SELECT YourTable.[name]
FROM YourTable
GROUP BY YourTable.[name]
PIVOT YourTable.[Tqty];
;-)
JeffCoachman
ASKER
@hainkurt sample data is above to the left, what I want, to the right
@regality
For each record I doubt I'll need more then 150 characters. The access version of the file is nearly 300megs. So your saying if I normalize my data I shouldn't have a problem. Have you actually been in a similar situation where doing that worked?
I've normalized data before, do you believe setting table relationships relevant just to get the concatenation out of the way?
@boag2000
I'll ask. Crosstab query stopped at 306 headers.
@regality
For each record I doubt I'll need more then 150 characters. The access version of the file is nearly 300megs. So your saying if I normalize my data I shouldn't have a problem. Have you actually been in a similar situation where doing that worked?
I've normalized data before, do you believe setting table relationships relevant just to get the concatenation out of the way?
@boag2000
I'll ask. Crosstab query stopped at 306 headers.
try this
select [name], 'a(' & nz(sum_qty_A,0) & '), b(' & nz(sum_qty_B,0) & ')' as classWqty
from
(select [name], sum([qty]) as sum_qty_A where class='A' group by [name]) a
left join
(select [name], sum([qty]) as sum_qty_B where class='B' group by [name]) b on a.[name]=b.[name]
how many class do you have?
select [name], 'a(' & nz(sum_qty_A,0) & '), b(' & nz(sum_qty_B,0) & ')' as classWqty
from
(select [name], sum([qty]) as sum_qty_A where class='A' group by [name]) a
left join
(select [name], sum([qty]) as sum_qty_B where class='B' group by [name]) b on a.[name]=b.[name]
how many class do you have?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can use crosstab query
save this query as q_classes
then you can use
SELECT name, "A(" & [A] & "), B(" & [B] & "), C(" & C & "), D(" & D & ")" as qty
FROM q_classes;
to get
name qty
joe A(20), B(13), C(5), D(7)
save this query as q_classes
then you can use
SELECT name, "A(" & [A] & "), B(" & [B] & "), C(" & C & "), D(" & D & ")" as qty
FROM q_classes;
to get
name qty
joe A(20), B(13), C(5), D(7)
name class qty
joe A 10
joe A 9
joe A 1
joe B 3
joe B 10
joe C 5
joe D 7
name Total Of qty A B C D
joe 45 20 13 5 7
oops, i forgot to post the query ;)
save this as q_classes and use the query posted above for final solution
save this as q_classes and use the query posted above for final solution
TRANSFORM Sum(classes.qty) AS SumOfqty
SELECT classes.name, Sum(classes.qty) AS [Total Of qty]
FROM classes
GROUP BY classes.name
PIVOT classes.class;
Hi Slothnet. Cool name ;)
To concat the fields in mysql, as you have in your example would be;
Select name, class, Tqty, QTY, name, concat( substring(class,1), '(' , (substring(Tqty,1), ')' ) as classWqty from yourtable
group by class, name
order by class, name;
That doesn't put classWqty output side by side.. but if you combine some of the above answers with this Mysql Concat query, maybe you could get the disired output.
Kind regards,
Kj
It may just be me, but I am a firm believer that mySQL will eat MS Access for breakfast AND lunch.
Give this a try.
Give this a try.
create table tbl (name varchar(10), class char(1), Tqty int, QTY int);
insert into tbl values ('joe', 'A', 20, 10);
insert into tbl values ('joe', 'A', 20, 9);
insert into tbl values ('joe', 'A', 20, 1);
insert into tbl values ('joe', 'B', 30, 30);
insert into tbl values ('mary', 'B', 30, 30);
select name, group_concat(x) classWQty
from
(
select name, group_concat(concat(class,'(',Tqty,')')) x
from
(select distinct name,class,Tqty from tbl) t1
group by name,class
) t2
group by name;
For what its worth, my query would have errored, 1 to many brackets.. but I'm sure Cyber has it again ;)
Select name, class, Tqty, QTY, name, concat( substring(class,1), '(' , substring(Tqty,1), ')' ) as classWqty from yourtable
group by class, name
order by class, name;
ck,
>>It may just be me, but I am a firm believer that mySQL will eat MS Access for breakfast AND lunch.
No, it's not just you :)
<soapbox>
But then again, that's like saying, "It may just be me, but I am a firm believer that a Lexus will eat a Toyota Corolla for breakfast AND lunch." Both are sedans, just as MySQL and Access are both databases, but both are very clearly in different classes of products, with different target audiences.
Both are legitimate products, and both have advantages as well as disadvantages. There are specific applications for which Access is better suited, and there are specific applications for which MySQL is better suited.
</soapbox>
BTW, a little database humor. Be forewarned that the video has some salty language, so if you are easily offended by that, or if such things are frowned upon at your workplace, exercise your discretion:
http://www.youtube.com/watch?v=b2F-DItXtZs
>>It may just be me, but I am a firm believer that mySQL will eat MS Access for breakfast AND lunch.
No, it's not just you :)
<soapbox>
But then again, that's like saying, "It may just be me, but I am a firm believer that a Lexus will eat a Toyota Corolla for breakfast AND lunch." Both are sedans, just as MySQL and Access are both databases, but both are very clearly in different classes of products, with different target audiences.
Both are legitimate products, and both have advantages as well as disadvantages. There are specific applications for which Access is better suited, and there are specific applications for which MySQL is better suited.
</soapbox>
BTW, a little database humor. Be forewarned that the video has some salty language, so if you are easily offended by that, or if such things are frowned upon at your workplace, exercise your discretion:
http://www.youtube.com/watch?v=b2F-DItXtZs
<ot>
@Patrick
Ah.. I see how it is being read.
>>It may just be me, but I am a firm believer that mySQL will eat MS Access for breakfast AND lunch.
I should have added a qualifier/disclaimer that this statement is expressly for the question/task at hand, that is, purely from a performance viewpoint against a sizeable database. I have nothing against Access.
</ot>
@Patrick
Ah.. I see how it is being read.
>>It may just be me, but I am a firm believer that mySQL will eat MS Access for breakfast AND lunch.
I should have added a qualifier/disclaimer that this statement is expressly for the question/task at hand, that is, purely from a performance viewpoint against a sizeable database. I have nothing against Access.
</ot>
No worries, ck. Blame it on the morning coffee not being 100% effective yet :)
ASKER
I'm not going to comment on what doesn't remotely seem is relevant to my question.
I didn't yet get a chance to try the other solutions.
@matthewspatrick
Indexes made it work. I originally sent in Tqty a different way by aggregating where string value gets set. I went through quite a bit of formal discovery to make it work, you achieve the same result without altering anything, nice
@cyberkiwi
Looking forward to try out the mysql statements though I'm done.
I didn't yet get a chance to try the other solutions.
@matthewspatrick
Indexes made it work. I originally sent in Tqty a different way by aggregating where string value gets set. I went through quite a bit of formal discovery to make it work, you achieve the same result without altering anything, nice
@cyberkiwi
Looking forward to try out the mysql statements though I'm done.
Haha awww told ;)
@Sloth
I didn't even get a reply to a comment that may help you understand mysql Concat, Have a go..
Select name, class, Tqty, QTY, name, concat( substring(class,1), '(' , substring(Tqty,1), ')' ) as classWqty from yourtable
group by class, name;
Good times ;)
Kj
@Sloth
I didn't even get a reply to a comment that may help you understand mysql Concat, Have a go..
Select name, class, Tqty, QTY, name, concat( substring(class,1), '(' , substring(Tqty,1), ')' ) as classWqty from yourtable
group by class, name;
Good times ;)
Kj
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.