SQL count* question

SELECT t_name AS 'Name', COUNT(t_name) AS 'Count' FROM Car
GROUP BY t_name

Edit...

Should have mentioned this is T-SQL for Microsoft SQL Server, but the principle should be the same.

---

SmokingBrain


[edited by - MonkeyChuff on November 19, 2003 11:57:55 AM]

ok this is my code now

(SELECT COUNT(*) FROM Car,sales_transaction 
		 WHERE st_sellprice is not NULL
		 GROUP BY c_brand, c_name )

The problem is when i add sales_transaction to the from it calculates whatever in the sales_transaction table . How do i stop it also calculating this?





[edited by - johnnyBravo on November 19, 2003 12:40:49 PM]

Since its been a long while since ive done SQL and just going by the other post, im taking a stab that it calculates everything because you have COUNT(*), doesnt * usually stand for everything, I would specifically state what you want to count and save as qty or something, then see if that works.

-Shane

tried putting column name in, samething

Could you at least post the details of the table designs for Car and SalesTransaction. Then at least we may be able to see how to join them, which would then give us a reasonable chance of answering your question.

---

SmokingBrain

if you can give me a join example, you could just use one or two of each table.....if you can be bothered

create table Car (
	C_VIN				char(10)		not null	primary key,
	C_Name				char(20)		not null,
	C_Brand				char(20)		not null,
	C_Type				char(20)		not null,
	C_Colour				char(15),
	C_YearOfManufacture		char(4)		not null,
	C_SecondHand			char(1)		not null
);

create table Sales_Transaction (
	ST_No				char(3)		not null	primary key,
	Cust_No				char(3)		not null,
	SP_No				char(3)		not null,
	C_VIN				char(10)	not null,
	ST_Date				date		not null,
	ST_SellPrice			DECIMAL(8,2),
	ST_TradeinPrice			DECIMAL(8,2),
	foreign key (Cust_No)  references Customer (Cust_No),
	foreign key (SP_No)    references Sales_Person (SP_No),
	foreign key (C_VIN)    references Car (C_VIN)
);

Make sure you are joining properly and count on a key in the transaction table. Something like this should do it:

select C_Brand,C_Name,count(ST_No) from Car natural join Sales_Transaction group by C_Brand, C_Name;

OK, the join example in T-SQL would be;

SELECT C.* FROM Car C
INNER JOIN Sales_Transaction ST ON C.C_VIN = ST.C_VIN

This will select all from Car where there is a matching Sales_Transaction record, based on the join criteria C_VIN. You have to join on C_VIN as this is the common data element between tables.

---

SmokingBrain

quote:

---

The problem is when i add sales_transaction to the from it calculates whatever in the sales_transaction table . How do i stop it also calculating this?

---

Add an ''and car.C_VIN = sales_transaction.C_VIN'' to your where clause.

i''m not trying to be a dick, but you''ve asked several questions about SQL in the past day or two... and it is all for homework, and SQL isn''t so hard that you couldn''t figure these out yourself by reading a tutorial or paying attention in class for one day.