Re: Group By over many colums

[Marco Neves]

Hi,

If it didn't existed and was created with the create temporary table it will 
desapear when you close your corrent session.

otherwise you can drop it with

DROP TABLE t_sends;

mpneves

On Thursday 19 January 2006 16:35, Critters wrote:
> Thanks!
> I wrote:
>
>
> DELETE FROM t_sends;
>
> CREATE TEMPORARY table IF NOT EXISTS t_sends
> (SELECT f1 as 'domain' from sends WHERE gameID = 1) union all
> (SELECT f2 as 'domain' from sends WHERE gameID = 1) union all
> (SELECT f3 as 'domain' from sends WHERE gameID = 1) union all
> (SELECT f4 as 'domain' from sends WHERE gameID = 1) ;
>
> SELECT MID(domain,INSTR(domain,'@')+1) AS 'domain' , count(*) from t_sends
> GROUP by 'domain'
>
>
> And that does the trick
>
> Is there any way to destroy the "t_sends" table?
> --
> Dave
>
>
> - Original Message -
> From: "Marco Neves" <[EMAIL PROTECTED]>
> To: "Critters" <[EMAIL PROTECTED]>
> Cc: 
> Sent: Thursday, January 19, 2006 4:20 PM
> Subject: Re: Group By over many colums
>
>
> Hi Critters,
>
> The problem is that as your MySQL is 4.0.21 don't suport the subselect you
> would need to do the group.
>
> I was thinking and you have another alternative:
>
> CREATE TEMPORARY table tdata
> (SELECT f1 as 'domain' from sends) union all
> (SELECT f2 as 'domain' from sends) union all
> (SELECT f3 as 'domain' from sends) union all
> (SELECT f4 as 'domain' from sends);
>
> -- This would create an temporary table with all the data
>
> SELECT domain,count(*) from tdata GROUP by domain;
>
> -- This sould do the trick.
>
> mpneves
>
> On Thursday 19 January 2006 16:14, Critters wrote:
> > Thanks for the replies Marco...
> > mysql  Ver 12.22 Distrib 4.0.21
> >
> > So that could be it?
> >
> > By the way...
> >
> > (SELECT f1 as 'domain' from sends) union
> > (SELECT f2 as 'domain' from sends) union
> > (SELECT f3 as 'domain' from sends) union
> > (SELECT f4 as 'domain' from sends)
> >
> > Works, and returns a list where f1, f2, f3 and f4 are all in the column
> > "domain"
> >
> > But as soon as I add "GROUP BY domain" to the end it fails. I have also
> > tried "GROUP BY 'domain'"
> > --
> > David Scott
>
> --
> AvidMind, Consultadoria Informática, Unipessoal, Lda.
> Especialistas em OpenSource
> http://www.avidmind.net
> OBC2BIP

-- 
AvidMind, Consultadoria Informática, Unipessoal, Lda.
Especialistas em OpenSource
http://www.avidmind.net
OBC2BIP

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Group By over many colums

[Marco Neves]

Hi Critters,

The problem is that as your MySQL is 4.0.21 don't suport the subselect 
you 
would need to do the group.

I was thinking and you have another alternative:

CREATE TEMPORARY table tdata
(SELECT f1 as 'domain' from sends) union all
(SELECT f2 as 'domain' from sends) union all
(SELECT f3 as 'domain' from sends) union all
(SELECT f4 as 'domain' from sends);

-- This would create an temporary table with all the data

SELECT domain,count(*) from tdata GROUP by domain;

-- This sould do the trick.

mpneves

On Thursday 19 January 2006 16:14, Critters wrote:
> Thanks for the replies Marco...
> mysql  Ver 12.22 Distrib 4.0.21
>
> So that could be it?
>
> By the way...
>
> (SELECT f1 as 'domain' from sends) union
> (SELECT f2 as 'domain' from sends) union
> (SELECT f3 as 'domain' from sends) union
> (SELECT f4 as 'domain' from sends)
>
> Works, and returns a list where f1, f2, f3 and f4 are all in the column
> "domain"
>
> But as soon as I add "GROUP BY domain" to the end it fails. I have also
> tried "GROUP BY 'domain'"
> --
> David Scott
-- 
AvidMind, Consultadoria Informática, Unipessoal, Lda.
Especialistas em OpenSource
http://www.avidmind.net
OBC2BIP

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Group By over many colums

[Marco Neves]

Hi,

Before anything else I would verify that your mysql-server is 4.1 or 
superior, as before that MySQL didn't suported sub-selects.

It looks to me that the error should be that.

After that I only spot the missing table alias before the Group by.

mpneves

On Thursday 19 January 2006 16:01, Critters wrote:
> The actual table is called "sends" and the data is like this:
> | id  | f1 | f2 | f3
> |
> |   3 |  foo.com  | yahoo.com|  |
> |   4 |  dsl.pipex.com |  foo.com| foo.com|
> |   5 |  vodafone.com| btinternet.com| co-op.co.uk  |
>
> I tired:
>
> SELECT domain, count(*)
> FROM (
> (SELECT f1 as domain from sends) union all
> (SELECT f2 as domain from sends) union all
> (SELECT f3 as domain from sends)
> )
> GROUP BY domain
>
> But I get:
>
> [localhost] ERROR 1064: You have an error in your SQL syntax.  Check the
> manual that corresponds to your MySQL
> server version for the right syntax to use near 'SELECT f1 as domain from
> sends) union all (SELECT f2 as domain
>
> Can you spot where I am going wrong?
> -
> David Scott
>
>
> - Original Message -
> From: "Marco Neves" <[EMAIL PROTECTED]>
> To: 
> Cc: "Critters" <[EMAIL PROTECTED]>
> Sent: Thursday, January 19, 2006 3:34 PM
> Subject: Re: Group By over many colums
>
>
> Hi,
>
> To this on I just see a solution, that depends on sub-selects, so it's
> available from Mysql 4.1 forward:
>
> SELECT name,count(*) from ((SELECT name1 name FROM ) UNION ALL
> (SELECT name2 name FROM ) UNION ALL (SELECT name3 name FROM
> )) tab GROUP by name;
>
> Hope this solves you problem.
>
> mpneves
>
> On Thursday 19 January 2006 15:16, Critters wrote:
> > Hi
> > I have a table setup like this:
> >
> > id, name1, name2, name3
> >
> > Which has data like this:
> >
> > 1, Dave, Bob, Simon
> > 2, Joe, Tim, Dave
> > 3, Dave, Bob, Tom
> >
> > I can run
> >
> > SELECT name, count(id) FROM  GROUP BY name1 ORDER BY count(id)
> > DESC
> >
> > Which would give me:
> >
> > Dave, 2
> > Joe, 1
> >
> > But how would I go about getting the following result:
> >
> > Dave, 3
> > Bob, 2
> > Tom, 2
> > Joe, 1
> > Simon, 1
> >
> > Where it groups by name1, name2 and name3? Is it possible?
> > -
> > David Scott
>
> --
> AvidMind, Consultadoria Informática, Unipessoal, Lda.
> Especialistas em OpenSource
> http://www.avidmind.net
> OBC2BIP

-- 
AvidMind, Consultadoria Informática, Unipessoal, Lda.
Especialistas em OpenSource
http://www.avidmind.net
OBC2BIP

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Group By over many colums

[Marco Neves]

Hi,

To this on I just see a solution, that depends on sub-selects, so it's 
available from Mysql 4.1 forward:

SELECT name,count(*) from ((SELECT name1 name FROM ) UNION ALL 
(SELECT name2 name FROM ) UNION ALL (SELECT name3 name FROM 
)) tab GROUP by name;

Hope this solves you problem.

mpneves

On Thursday 19 January 2006 15:16, Critters wrote:
> Hi
> I have a table setup like this:
>
> id, name1, name2, name3
>
> Which has data like this:
>
> 1, Dave, Bob, Simon
> 2, Joe, Tim, Dave
> 3, Dave, Bob, Tom
>
> I can run
>
> SELECT name, count(id) FROM  GROUP BY name1 ORDER BY count(id)
> DESC
>
> Which would give me:
>
> Dave, 2
> Joe, 1
>
> But how would I go about getting the following result:
>
> Dave, 3
> Bob, 2
> Tom, 2
> Joe, 1
> Simon, 1
>
> Where it groups by name1, name2 and name3? Is it possible?
> -
> David Scott

-- 
AvidMind, Consultadoria Informática, Unipessoal, Lda.
Especialistas em OpenSource
http://www.avidmind.net
OBC2BIP

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Interesting Query Problem

[Marco Neves]

Hi,

An alternative for any MySQL version (from 3.23.??) would be:

SELECT r1.question_id,count(r1.member_id)
FROM Records r1
LEFT JOIN Records r2 ON r1.question_id=r2.question_id
AND r2.member_id=
WHERE r2.question_id IS NULL;

 must be the member name.

mpneves

On Thursday 19 January 2006 11:18, Gleb Paharenko wrote:
> Hello.
>
> Perhaps this will work (depends on the version of MySQL you're using):
>
> select question_id
>   , count(*)
> from Records
> group by question_id
> having question_id not in (
>   select distinct question_id
>   from Records r
>   where member_id = @current_member_id);
>
> @current_member_id equals to current_user
>
> G G wrote:
> > Hello,
> >
> > I have a simple Records table with two columns, member_id and
> > question_id.
> >
> >
> >
> > The object of the query is to retrieve the question_id, as well as how
> > many times it's been answered - as long as the current user hasn't
> > answered it (member_id).  So, the query shouldn't return any
> > question_id's (and counts) if it has been answered by the current user.
> >
> >
> >
> >  Right now I have this:
> >
> > SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY
> > question_id;
> >
> >
> >
> > I've tried throwing in different variants of 'WHERE member_id != X', but
> > all that seems to return is the count of questions answered, minus the
> > amount of times the particular user has answered them.  For example, if
> > user X has answered a question that had been answered another 50 times,
> > my query will still return that question_id, but with a count of 49.
> >
> >
> >
> > Your help is appreciated in advance.  Thanks!
> >
> >
> >
> >
> >
> > Kind Regards,
> >
> > Gerald Glickman
> >
> >
> >
> > G2 Innovations.com, Inc.
> >
> > http://www.g2innovations.com 
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
><___/   www.mysql.com

-- 
AvidMind, Consultadoria Informática, Unipessoal, Lda.
Especialistas em OpenSource
http://www.avidmind.net
OBC2BIP

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Database design help

[Marco Neves]

Ian,

I'ld like to help you, but a more specific db design would depend on 
more 
specific description on your application needs.

What I can say is that you need to adapt your database to your reality.

What I got til now is that you need a product table, where you can 
store your 
basic information on products.

You say you have other information, but I could understand several 
things.

1- That other information is related to the product, to the 
transaction, to 
both, to stocks?

for example, color or size is relevant to determine stocks and 
is related to 
the product, and so is relevant to the transactions also.

The sale rep is relevant to transaction, but not to the product.

sales rep comission is relevante to the sales rep, but not to 
the 
transaction nor the product.

My point is, a database design can be a complex task, and the hability 
an 
application will have to provide solutions to the real world depends, before 
anyother thing in that database design.

The is the point where almost all analisys most be done, and almost no 
programming (i think).

mpneves

On Wednesday 18 January 2006 22:55, you wrote:
> Thanks Ed.  That's another good idea.  The consensus I'm getting is to
> create one table that stores unchanging data about the product and another
> that stores transaction details.  The
> problem I'm still having is how to efficiently handle more than one
> changing value.
>
> As an example, let's say I want to keep track of not only the quantity of a
> product but who the sales rep for that product is.  While the quantity
> would change much more frequently than the sales rep I could put both in
> the same transaction table, but then I'll end up with duplicated data.  For
> example,
>
> date | product_id | quantity | rep
> 2006-01-01 | 1 | 100 | rep 1
> 2006-02-01 | 1 | 98 | rep 1
> 2006-03-01 | 1 | 98 | rep 2
> 2006-04-01 | 1 | 50 | rep 2
>
> Alternatively, I could create one table for the quantity and another for
> the sales rep.
>
> date | product_id | quantity
> 2006-01-01 | 1 | 100
> 2006-02-01 | 1 | 98
> 2006-04-01 | 1 | 50
>
> date | product_id | rep
> 2006-01-01 | 1 | rep 1
> 2006-03-01 | 1 | rep 2
>
> This seems to be the cleanest solution, other than requiring a table for
> every field that I want to track.
>
> Ian
>
> At 02:36 PM 1/18/2006 -0800, Ed Reed wrote:
> >I built my inventory system like this,
> >
> >I have a products table that contains all the information specific to each
> >part, less the quantity, i.e. Part Number, Description, Vendor, Color,
> >Weight, SKU number, etc...
> >
> >Then I have another table that is my Inventory Tranactions Log that is
> >just the following
> >
> >Date, ProductID, Qty, TypeOfTranacstion, Comment
> >
> >The inventory for each part may adjust daily or not. When parts are
> >removed/sold the transaction log gets a record for that product and the
> >number of parts that were sold and the type of transaction that occurred.
> >When parts are received another transaction is entered for that part with
> >the quantity received and the type of transaction that occurred. When we
> >close the store and want to take a full inventory we first run a report
> >that get the sums of all the transactions for each product and that tells
> >us what should be on the shelf according to the database. Then we verify
> >or adjust the qty for each product on the shelf by adding a record to the
> >transaction log indicating the quantity and the type of transaction that
> >occurred.
> >
> >When we want to see the values in the inventory its a very simple report
> >to get the sums for each product.
> >
> >- Hope that helps.
> >
> > >>> Ian Klassen <[EMAIL PROTECTED]> 1/18/06 10:09:55 AM >>>
> >
> >Hi all,
> >
> >I'm trying to figure out a solution to the following problem.
> >
> >Let's say I have a store with various products. I take inventory of these
> >products on different days. At any given time I want to view what the
> >inventory is for the entire store. I also want to know whether the
> >inventory in the result was taken on that day or was carried forward from
> > a previous date. I may also have to make changes to the inventories
> > previously recorded. I have a few solutions, none of which I'm really
> > happy with.
> >
> >The first is to create rows that contain the inventory for each product on
> >a given day. If no inventory was taken for a given product then leave the
> >column null.
> >
> >date_of_inventory| product a| product b| product c
> >2006-01-02| 100| 50| 25
> >2006-01-03| NULL| 45| NULL
> >2006-01-05| 78| NULL| 22
> >
> >To obtain the inventory on any given day I would have to query each
> > product and find the most recent time that it was updated. With this
> > solution there is a lot of wasted space caused by the NULL's.
> >
> >Another solution would be to have a start and end date for when the
> >inventory is valid such as:

Re: Database design help

[Marco Neves]

Hi,

Why don't you create two table:

 * a product table, with the product discriptions, and other product 
related 
info (call it prod):
|ID|NAME|SOME|OTHER|FIELDS|
|1|ProdA|..|..|..|
|2|ProdB|..|..|..|

* a stock movements table, with moviments by product (call it pro_move):
|ID|PROD__ID|DAY|MOV|DESCRIPT|
|1|1|2006-01-01|10|Inventory at Jan 1st for Prod A|
|2|2|2006-01-01|25|Inventory at Jan 1st for Prod B|
|3|1|2006-01-02|-5|Selled 5 units of A at Jan 2nd|

Then to know the inventary to up-to-date of every product you can do:

SELECT p.id,p.name,sum(pm.mov),max(day)
FROM prod p LEFT JOIN prod_move pm
ON p.id=pm.prod__id
GROUP by p.id;

If you think your product or move table will grow too big you can add a 
stock 
column to the prod table and update that field when you add a movement to 
your prod_move table, and verify that value from time to time (and if 
possible just add movement in transaction, with both tables suporting them - 
InnoDB ou DBD).

This is the way I would do it.

What you think?

mpneves

On Wednesday 18 January 2006 18:09, Ian Klassen wrote:
> Hi all,
>
> I'm trying to figure out a solution to the following problem.
>
> Let's say I have a store with various products.  I take inventory of these
> products on different days.  At any given time I want to view what the
> inventory is for the entire store.  I also want to know whether the
> inventory in the result was taken on that day or was carried forward from a
> previous date.  I may also have to make changes to the inventories
> previously recorded.  I have a few solutions, none of which I'm really
> happy with.
>
> The first is to create rows that contain the inventory for each product on
> a given day.  If no inventory was taken for a given product then leave the
> column null.
>
> date_of_inventory | product a | product b | product c
> 2006-01-02| 100   | 50| 25
> 2006-01-03| NULL  | 45| NULL
> 2006-01-05| 78| NULL  | 22
>
> To obtain the inventory on any given day I would have to query each product
> and find the most recent time that it was updated.  With this solution
> there is a lot of wasted space caused by the NULL's.
>
> Another solution would be to have a start and end date for when the
> inventory is valid such as:
>
> start_date| end_date  | product a | pa_up | product b 
> | pb_up | product c
>
> | pc_up
>
> 2006-01-02| 2006-01-03| 100   | TRUE  | 50
> | TRUE  | 25| TRUE
> 2006-01-03| 2006-01-05| 100   | FALSE | 45| TRUE  
> | 25| FALSE
> 2006-01-05| 2006-01-05| 78| TRUE  | 45
> | FALSE | 22| TRUE
>
> With this solution I can quickly retrieve the inventory on any given day
> and see what inventory was taken on that day (which product update columns
> are set to TRUE).  However, I see the update side of this as a nightmare
> (especially considering I'm duplicating data).
>
> A third solution could be breaking each product into its own table.  This
> would eliminate the issues with the first two solutions but I would end up
> with hundreds of tables which I would like to avoid.
>
> Any help on the direction that I should go would be greatly appreciated.
>
> Ian

-- 
AvidMind, Consultadoria Informática, Unipessoal, Lda.
Especialistas em OpenSource
http://www.avidmind.net
OBC2BIP

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SELECT Row Numbers?

[Marco Neves]

oix ppl,

now I have a question.

I thougth that when you make a SELECT without an ORDER BY, the rows 
were 
returned in natural order, that would be some specific order (insertion 
order, presence in file, I don't know, but the order would be always the 
same).

Is this uncorrect?

Is the returning order variable?

Thanks, 

mpneves

On Tuesday 10 May 2005 10:37, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
>
> [EMAIL PROTECTED] writes:
> > hi,
> > your need is:
> > select * from temp LIMIT 3,4;
> > -- 3 because you have to take the fourth and 4 because dist=3+1
>
> This does not make sense.  A SELECT without an ORDER BY returns the
> rows in some undefined order.  If you use "LIMIT 3,4" without ORDER BY, you
> get four rows out of an unordered set, so it's virtually identical to
> "LIMIT 4".  As long as the original poster doesn't say what ordering
> he wants, there's no way to tell him a solution.

-- 
Marco Paulo Neves
MySQL Core Certified
Linux Certified Professional
http://themage.bliker.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ROW_SIZE or something alike

[Marco Neves]

oix ppl,

 Nobody have any sugestion that can me help with this issue?

 thanks

mpneves
On Wednesday 16 March 2005 14:09, Marco Neves wrote:
> oix ppl,
>
>  sorry for the question, but I looked in the Manual online and didn't find
> nothing that do this that I'm looking for, so I'm hopping someone can help
> me.
>
>  I'm looking for some way to know the size each row of a table uses in my
> database (phisical - real disk space allocated or logical - datasize
> ignoring compression and any control data, don't mind, anything is better
> than nothing).
>
>  The way I was thinking this could be got was with something like:
>
>  SELECT ROW_SIZE(),* FROM tablename WHERE somefield=somevalue;
>
>  or
>
>  SELECT group__id, SUM(ROW_SIZE()) grpsize FROM tablename GROUP BY
> group__id;
>
>  This is what I thought would be great to have, but any other way to get a
> row size would be good enought.
>
>  This could be both Data and Index size for each row, but data would be
> good enought.
>
>  There is anyway to get this information?
>
>  Thanks,
>
> mpneves
> --
> Marco Paulo Neves
> MySQL Core Certified
> Linux Certified Professional
> http://themage.bliker.com

-- 
Marco Paulo Neves
MySQL Core Certified
Linux Certified Professional
http://themage.bliker.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ROW_SIZE or something alike

[Marco Neves]

oix ppl,

 sorry for the question, but I looked in the Manual online and didn't find 
nothing that do this that I'm looking for, so I'm hopping someone can help 
me.

 I'm looking for some way to know the size each row of a table uses in my 
database (phisical - real disk space allocated or logical - datasize ignoring 
compression and any control data, don't mind, anything is better than 
nothing).

 The way I was thinking this could be got was with something like:

 SELECT ROW_SIZE(),* FROM tablename WHERE somefield=somevalue;

 or 

 SELECT group__id, SUM(ROW_SIZE()) grpsize FROM tablename GROUP BY group__id;

 This is what I thought would be great to have, but any other way to get a row 
size would be good enought.

 This could be both Data and Index size for each row, but data would be good 
enought.

 There is anyway to get this information?

 Thanks,

mpneves
-- 
Marco Paulo Neves
MySQL Core Certified
Linux Certified Professional
http://themage.bliker.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]