0

Good day / Night!

I am getting ready for last test which will be tomorrow. Since I have idea, what will be on test, I wanted to ask for your help on subject(Procedures). (They are my weak spot).

Here are my tables:

Discount_cards

Create table Discount_cards
(
Card_code Int Auto_Increment Primary key,
Number char(9),
Sum Decimal(10,2),
Discount Int
);

Purchases

Create table Purchases
(
SellCode Int Auto_Increment Primary key,
Date date,
Quantity Int,
PricePerUnit Decimal(10,2),
Discount Int,
Foreign key (Discount) references Discount_cards(Card_code) On delete cascade
);

So, here is my problem.

Basically, we know how my table.Purchases looks like. (I mean colon names.)

Now, I need to create procedure which will SELECT [Date, PricePerUnit, Quantity] where year(Date) = 2015.

Problem

It would be very easy to just select all of these columns, but that would be too easy right?

Here is how it would look like:

delimiter //

CREATE PROCEDURE procedure1 (OUT X INT)
BEGIN
  Select 'Date', 'Quantity', 'PricePerUnit' from Purchases where year(Date) = 2015;
END//
delimiter ;
CALL procedure1(@z);
Select @z;

Since that's just too easy, I need to Select this same columns, but rename them, and add Total.

Here is how it should look like:

delimiter //

CREATE PROCEDURE procedure2 (OUT X INT)
BEGIN
  Select 'Date' as Purchase_date where year(date) = 2015;    *Here is problem*
END//
delimiter ;
CALL procedure2(@z);
Select @z;

So yeah... here is my problem. I know how to Select date and rename it as Purchase_date, but how do I Select multiple Colons with their custom names?

And last but not least, How to add Total in this procedure.

Thank you for your time.

Vairis
  • 227
  • 3
  • 13
  • you already know how to select multiple "colons" (perhaps you mean "columns"?). every field you select can be aliased. you're not limited to one single alias per query. – Marc B Jun 20 '16 at 20:42
  • Yes I meant columns*. As for I am not limited to single alias, I know. My problem is, I don't know how to do multiple aliases. Also, can you link to "original" post? Since this is duplicate? – Vairis Jun 20 '16 at 20:47
  • 1
    you already know how to do one alias. more is just the same thing. `select field1 as alias1, field2 as alias2, etc...` – Marc B Jun 20 '16 at 20:50
  • Thank you!, but what about Total? How do I add it in the end? – Vairis Jun 20 '16 at 20:54
  • Total as Total = field1 * field2 or something? – Vairis Jun 20 '16 at 20:55
  • 1
    no. it's always `field AS alias`. whether field is actually a field name or a calculation doesn't matter. the alias always comes at the end. `(foo+bar*sum(baz)) AS alias` – Marc B Jun 20 '16 at 21:57

0 Answers0