[SOLVED] Question regarding couting results in a query?

[Solarpitch]

Hey,

 

I have a table that has a list of items that were sold on a POS system. Each item has an associated receipt number so the table would look like..

 

ID      PRODUCT      PRICE     RECEIPT NUMBER
---------------------------------------------
1       Budwiser       4.80       001
2       Carlsberg      4.80       001
3       Coke            2.20       001
4       Heineken       4.80       002
5       Coke            2.20       002

 

You can see that there are 3 items in the first sale (receipt 001) and two items in the second sale (receipt 002)

I'm basically looking to count the number sales in the table, not the rows.

 

There are 5 rows but only 2 sales. Wouls I use a group by clause to achieve this?

 

I think the query would need to group all the reciept numbers that are the same and count them as 1 sale?

 

 

[RussellReal]

SELECT COUNT(`receipt_number`) FROM `POS_system` GROUP BY `receipt_number`

[Solarpitch]

I tried that already but that just counts the all the rows. In my example it would return 5 and not 2

 

EDIT: Even when I remove the group by clause I still get the same result.

[gevans]

That select statement should work, can you post your code?

[Solarpitch]

This is the function here. (I'm using the Codeigniter Framework so the rest of the code just prints to screen)

 

It's like it doesnt even take the group by into consideration and just returns the total rows.

 

<?php

function total_sales($date)
{   

$query = $this->client->query('SELECT COUNT(ticketnumber) as total FROM sales WHERE date = '.$date.' GROUP BY "ticketnumber"');

$row = $query->row();
return $row->total;

}

?>

[RussellReal]

GROUP BY "ticketnumber" IS WRONG

below is correct

GROUP BY `ticketnumber`

[Solarpitch]

Cool.. but not sure what happening now. There are 52 sales in the table but its now only returning a count of 1. I'll see if I can figure it out.

[Solarpitch]

Right I see what was happening... it's returning it like

 

3

2

1

4

etc..

 

Is there something I can add into that query to get the sum of them then?

[RussellReal]

"SELECT COUNT(ticketnumber) as total, SUM(price) FROM sales WHERE date = '.$date.' GROUP BY `ticketnumber`

[Solarpitch]

duh.. should have know that. Thanks!