i have a ms database with 4 columns, id, name, purchase_amount, purchase_quantity.

Want i want to be able to do is group by using the name, but where it groups one particular user, i would like to be able to add the purchase_amount column together and show it as 1 total.

can this be done?

Hi Simon

Something like this:

SELECT id, name, SUM(purchase_amount), purchase_quantity column
FROM myTable
GROUP BY name

Digga

Sharing Knowledge Saves Valuable Time!!!

works like a charm.

thanks very much for your help. Your a star

regards

simon

You're welcome!

Digga

Sharing Knowledge Saves Valuable Time!!!

another cry for help.

it is working fine as per your advice thanks but what i would like to add in now is be able to count the amount of rows that contain a certain value (name) so it could show me the value amount but also the amount of sales to that particular person.

any advice.

cheers

simon

Try this, might be what you want:

SELECT COUNT(id) AS 'Quantity', name as 'Name', SUM(purchase_amount) AS 'Purchase Total'
FROM myTable
Group By name
Order By name

Digga

Sharing Knowledge Saves Valuable Time!!!

sql is as

SELECT COUNT(ID_Company_name) AS 'Quantity', ID_Username as 'ID_Username', SUM( ID_Purchase_amount) AS 'Purchase Total'
FROM Tbl_UserData
GROUP BY ID_Company_name
ORDER BY ID_Company_name

but am getting the error when tested 'you tried to execute a query that does not include the specified expression 'ID_Username' as part of an aggregate function'

what does it mean?

It means that you cannot include the field ID_Username without performing some kind of function on the field - Group By rules I'm afraid.

Can you explain what output you would like to see?

SELECT COUNT(ID_Company_name) AS 'Quantity', ID_Username as 'ID_Username', SUM( ID_Purchase_amount) AS 'Purchase Total'
FROM Tbl_UserData
GROUP BY ID_Company_name
ORDER BY ID_Company_name

Digga

Sharing Knowledge Saves Valuable Time!!!

GOt it in the end. used this:

SELECT COUNT(ID_Company_name) AS 'Quantity', SUM( ID_Purchase_amount) AS 'Purchase Total', ID_Company_name AS 'Company'
FROM Tbl_UserData GROUP BY ID_Company_name
ORDER BY ID_Company_name

what this gives allows me to do is show companies registered, the amount of purchases they have made and the total value of these purchases.

It seems to work for the minute.

Once again thanks for your help.