Selecting Distinct issues... help!

[iPixel]

Ok here's what's giving me a problem.

 

I have a table with call records, which looks something like so.

 

[CALLDATE]  [COUNT_PNUMS] [CALLTYPE]  [FILENUMBER]

12/04/2009            11                    I                  123456

12/04/2009              5                    O                123456

12/01/2009            7                      I                  987654

 

My issue is, im trying to select a range of dates let's say between 12/01/2009 and 12/04/2009

That's the easy part... but then i want to display these dates cingularly, and not the amount of times they were pulled.

 

EXAMPLE:

SELECT calldate, count_pnums, calltype, filenumber FROM tablename WHERE calldate BETWEEN '$sDate' AND '$eDate' AND filenumber = '$filenum'";

 

So if i run a while loop echoing all my results... in this case 12/04/2009 will be echoed twice.

But for my purposes i only need to show it once, so i thought i could use a DISTINCT/UNIQUE clause in the query but that does not work when selecting multiple fields. So how can i do this while still pulling more than 1 field?

 

The above result would be like

 

12/04/2009  -  11  -  I  -  123456

12/04/2009  -  5    -  O  -  123456

12/01/2009  -  7    -    I  -  987654

 

But i need it to display like ...

 

12/04/2009  -  x  -  x  -  123456

12/01-2009  -  x  -  x    - 987654

 

The reason i left those #'s as X's is because during the do while loop i'd compute the totals per day.

 

Thanks!

[fenway]

First problem -- that's not a valid mysql date format -- use STR_TO_DATE().

[iPixel]

Yes i realise that, it's actually an ORACLE query, but the logic is the same... and more people are prone to helping here than in the other Forum.

[premiso]

Well the calltype is definitely causing the issue. Technically the query is distinct, since one call type is I the other is O. There is no way around this, as far as I know other than leaving that field off. You may be able to do some fancy sub-querying (such as displaying total 16 - 11 I - 5 O in one line but I do not think it would be worth it, in my opinion.)

 

 

[Zane]

Just throwing it out there but couldn't you use GROUP BY calldate... rather than selecting distinct

[PFMaBiSmAd]

In mysql you would do the following (unknown if there is equivalent in ORACLE) -

 

SELECT calldate, SUM(count_pnums) AS total, filenumber FROM tablename
WHERE calldate BETWEEN '$sDate' AND '$eDate' GROUP BY calldate, filenumber