Display all records and totals at same time

[bhutz]

Hi Guys,

Is it possible for me to do something like this...
I have this sort of data...

12/10/2005 16:22:00 Mr Smith Title: Mr...
12/10/2005 17:39:00 Mrs Banks Title: Mrs...
12/10/2005 18:32:00 Mrs Banks Title: Mrs...
13/10/2005 16:29:00 Mr Banks Title: Mr...
13/10/2005 16:54:00 Miss Banks Title: Miss...
14/10/2005 10:28:00 Miss Banks Title: Miss...
14/10/2005 12:15:00 Ms Banks Title: Ms...
14/10/2005 12:41:00 Mr Jones Title: Mr...
14/10/2005 17:26:00 Miss Jones Title: Miss...
15/10/2005 09:30:00 Mr Jones Title: Mr...
17/10/2005 14:31:00 Mr Smith Title: Mr...
18/10/2005 12:20:00 Mr Smith Title: Mr...
18/10/2005 16:12:00 Mr Smith Title: Mr...
18/10/2005 20:42:00 Miss Smith Title: Miss...

that I return from a database query but some why I would like to also display how many messages were sent in AM or PM.
The query to get these totals is easy enough but when I use a group not all of the records are displayed.

Would it be possible to somehow include the totals in the resultset somehow using a join or something so I end up with something like this, although the records are duplicated at least I will have a record of the count of day and night messages.

DATE		TIME		RESULT		CLIENT CHARGE	DAY	NIGHT
05/10/2005	23:06	Patch Successful	6426	3.00	2	10
10/10/2005	19:56	Patch Successful	6426	3.00	2	10
10/10/2005	20:12	Patch Successful	6426	3.00	2	10
12/10/2005	19:26	Patch Successful	6426	3.00	2	10
14/10/2005	09:30	Patch Successful	6426	3.00	2	10
14/10/2005	19:01	Patch Failed	6426	0.90	2	10
15/10/2005	19:46	Patch Successful	6426	3.00	2	10
16/10/2005	13:57	Patch Successful	6426	3.00	2	10
25/10/2005	00:29	Patch Successful	6426	3.00	2	10
31/10/2005	20:42	Patch Successful	6426	3.00	2	10

[r937]

two different sets of data, two different queries, probably two different threads

could you focus on one set of data please, and please show a sample of how you want the totals to be displayed together with the details

[bhutz]

Sorry I should have double checked this one...I think I messed up because I am trying to do the same thing with 2 different tables, hence the 2 different examples of data.
To explain again I have a table with messages in and I am trying to retrieve messages for a given client, like so

DATE		TIME		PHONE		RESULT		CLIENT	CHARGE
2005-10-05	23:06	0800 123 123	Patch Successful	6426	3.00
2005-10-10	19:56	0800 123 123	Patch Successful	6426	3.00
2005-10-10	20:12	0800 123 123	Patch Successful	6426	3.00
2005-10-12	19:26	0800 123 123	Patch Successful	6426	3.00
2005-10-14	09:30	0800 123 123	Patch Successful	6426	3.00
2005-10-14	19:01	0800 123 123	Patch Failed	6426	0.90
2005-10-15	19:46	0800 123 123	Patch Successful	6426	3.00
2005-10-16	13:57	0800 123 123	Patch Successful	6426	3.00
2005-10-25	00:29	0800 123 123	Patch Successful	6426	3.00
2005-10-31	20:42	0800 123 123	Patch Successful	6426	3.00

Using the following query

SELECT DISTINCT `DATE`,`TIME`,PHONE, RESULT, CLIENT, CHARGE FROM mysqlPATCHLOG WHERE CLIENT = '6426' AND MONTH(DATE) = 10 AND YEAR(DATE) = 2005 ORDER BY DATE,TIME

I also wanted to be able to determine how many of these messages were night messages and how many day so that I could end up with something like this

DATE		TIME		PHONE		RESULT		CLIENT	CHARGE DAY	NIGHT
2005-10-05	23:06	0800 123 123	Patch Successful	6426	3.00		2	10
2005-10-10	19:56	0800 123 123	Patch Successful	6426	3.00		2	10
2005-10-10	20:12	0800 123 123	Patch Successful	6426	3.00		2	10
2005-10-12	19:26	0800 123 123	Patch Successful	6426	3.00		2	10
2005-10-14	09:30	0800 123 123	Patch Successful	6426	3.00		2	10
2005-10-14	19:01	0800 123 123	Patch Failed	6426	0.90		2	10
2005-10-15	19:46	0800 123 123	Patch Successful	6426	3.00		2	10
2005-10-16	13:57	0800 123 123	Patch Successful	6426	3.00		2	10
2005-10-25	00:29	0800 123 123	Patch Successful	6426	3.00		2	10
2005-10-31	20:42	0800 123 123	Patch Successful	6426	3.00		2	10

I know the query I need to show how many messages were between a certain time to determine night and day messages but can't seem to combine the two (totals and actual rows)

I guess what I really want to know is how to be able to display all messages but at the same time determine totals. THe group by function seems to prevent me from doing this so I thought maybe a join of some sort might work?

[r937]

okay, i see the totals now (didn't scroll right before)

nope, you can't do them that way

what i was hoping for was that you'd want them interspersed with the details, sort of like this --

DATE       TIME  RESULT           CHARGE
2005-10-14 09:30 Patch Successful 3.00
2005-10-16 13:57 Patch Successful 3.00
DAYTIME TOTALS: 2
2005-10-14 19:01 Patch Failed     0.90
2005-10-12 19:26 Patch Successful 3.00
2005-10-15 19:46 Patch Successful 3.00
2005-10-10 19:56 Patch Successful 3.00
2005-10-10 20:12 Patch Successful 3.00
2005-10-31 20:42 Patch Successful 3.00
2005-10-05 23:06 Patch Successful 3.00
2005-10-25 00:29 Patch Successful 3.00
NIGHTTIME TOTALS: 8

even this would be difficult to do with sql

my suggestion: use application reporting software

[bhutz]

oh boo
To explain further )) I have to put the list of messages on a PDF, which has been done but there is also a totals section. So it doesn't really matter where I have the daytime and night time totals I just need the values.
Looks like I will have to just use two queries, not ideal but I have a quick look for what you suggested but can't find anything just yet.

I'll keep looking but if you had some specific reporting software in mind please can you name them. Wouldn't this software have to perform just as many queries as I would to retrieve this data anyway?

[r937]

i was thinking of something like Crystal Reports, but don't spend too much time looking, if you are rolling your own then just go with two queries

interspersing the totals with the details would have needed two queries too, except they would be combines in a UNION

[bhutz]

Thanks for the input...I think 2 queries shouldn't be all that slow but for future reference wanted to know if there was a better method really