SQL Question?

Question

How do i write a sql statement
s/n item Name qty
1 banana 5
2 mango 3
3 banana 2
4 apple 2
5 banana 6
how do i write a sql statement where it will sum the fruits and only show it one time as below

s/n item Name qty
1 banana 13
2 mango 3
3 apple 2

Thank you

Answer 1

SELECT [item Name], SUM(qty) AS qty
FROM table
GROUP BY [item Name], qty

In the SQL above, you can't include s/n because doing so will not allow you to sum the unique records.

Also, I hope these aren't your actual column names. Column names should contain only letters and characters (not spaces and slashes and whatnot). I've used brackets to delimit column names in case those are your real column names.

Answer 2

Don't include the serial number in the sql statement
Select [item name], sum([qty]) as total
Group By [item name]

Answer 3

First SQL statement (assuming that the table name is Fruits):

Select SerialNumber, ItemName, Qty
From Fruits
Order By SerialNumber;

Second:

Select SerialNumber, ItemName, Sum(Qty)
From Fruits
Order by SerialNumber
Group By SerialNumber, ItemName;

Answer 4

select count(*) cnt,x.itemname,x.SQTY from
(
select itemname,sum(qty) SQTY from fruits
group by itemname
) x,
(
select itemname,sum(qty) SQTY from fruits
group by itemname
) y
where x.sqty<=y.sqty
group by x.itemname,x.sqty
order by cnt

You can study much more tricks at SQL Exercises (http://www.sql-ex.ru/ )