Displaying three different columns from three Different Tables in SQL

Question

I'm trying to display three different columns in SQL all from the same database but each from different tables. One is called Name which I would also like to have the output grouped by, another column is a calulation between two columns called Money, and the third column is Date. NameId is what connects Name and the Money Column, and MoneyId connects Money to Date

I'm creating this on dbVisualizer, The three columns are all parts of different tables inside the same database. How can I display the calculated Money coulmn with its correct date, while being grouped by the Name Column

I know this is incorrect but it's what I have so far. How can I have the money calculation but not have to throw it's attributes in the group by function for it to run.

Select A.Name,
MAX(B.BillsPaid)As Paid, 
MAX(B.BillsSent) As Sent, C.DateId As Date_Id,
-- This is the money Calculation 
(CAST(BillsPaid As decimal(5,0))/CAST(nullif(BillsSent, 0) As 
decimal(5,0))) 
* 100 as Money
From B
Inner Join A on B.NameId = A.NameId
Inner Join C on B.MoneyId = C.MoneyId
Group By A.Name, C.DateId, B.BillsPaid, B.BillsSent

I would like to see this in the display. I'm new to SQL so all help is highly appreciated, thank you.

 Name          Money(%)      Date
John Doe        87%         June 2019

Instead I see

 Name       Paid      Sent     Money     Date 
John Doe     2          4       50%     June 2019
John Doe     1          4       25%     June 2019
John Doe     3          4       75%     July 2019

I want the money to be grouped by each name, for each date, however each name has more then one paid/sent per month

Why are you grouping **and** aggregating on `B.BillsPaid` and `B.BillsSent`? — Thom A, Jun 21 '19 at 14:33
I need the percentage of bills paid, and if I don't throw them in the group by I get an error, and I dont want to have to group them — user11681655, Jun 21 '19 at 14:35
What is the error if you don't group them? Putting something inside a `GROUP BY` and aggregating the column is completely redundant. You could simply remove the `MAX` and the `GROUP BY` and use `DISTINCT` and you would get identiical results here. — Thom A, Jun 21 '19 at 14:41
Definitely a little sample data to go with the expected output would help, but if I had to guess, I think you want to remove the `MAX(..)` lines, `SUM()` both the fields inside your `Money` equation, and remove `BillsPaid` and `BillsSent` from your grouping. — Aaron Dietz, Jun 21 '19 at 14:46
Thank you for the feedback. I removed Max(..) and replaced it with SUM(..), and removed those columns from the group by however I still get an error that 1) [Code: 8120, SQL State: S1000] Column 'BillsPaid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Same error with BillsSent — user11681655, Jun 21 '19 at 14:57
The problem is that each Name has multiple bills paid and sent so when I replace max and group by with distinct it displays the name but it doesn't group every billed paid and sent with each name but displays every bill sent/paid and shows the same name many times. — user11681655, Jun 21 '19 at 15:05
If you modify your question with sample data, you will get your answer straight away. — M. Kanarkowski, Jun 21 '19 at 15:14

Toni H · Answer 1 · 2019-06-21T18:09:33.953

0

I believe this gets you what you want - cross apply is a little weird, but it's handy :) If you want more information on that one, there's plenty to Google!

Select
    A.Name
    ,b.[Paid]
    ,b.[Sent]
    ,min(C.DateId) [Date_Id]
    ,(CAST(b.paid As decimal(5,0))/CAST(nullif(b.[sent], 0) as decimal(5,0))) * 100 [Money]
From
    a
    cross apply (
        select
            max(billspaid) [Paid]
            ,max(billssent) [Sent]
        from
            B
        where
            A.NameId = B.NameId
    ) b
    inner join C
        on B.MoneyId = C.MoneyId
group by
    A.Name
    ,b.[Paid]
    ,b.[Sent]

EDIT

Select
    a.Name
    ,b.[Paid]
    ,b.[Sent]
    ,min(C.DateId) [Date_Id]
    ,(CAST(b.paid As decimal(5,0))/CAST(nullif(b.[sent], 0) as decimal(5,0))) * 100 [Money]
From
    a
    cross apply (
        select
            max(billspaid) [Paid]
            ,max(billssent) [Sent]
        from
            B
        where
            A.NameId = B.NameId
    ) b
    inner join b b1
        on a.nameid = b1.nameid
    inner join c
        on b1.MoneyId = c.MoneyId
group by
    a.Name
    ,b.[Paid]
    ,b.[Sent]

edited Jun 21 '19 at 18:09

answered Jun 21 '19 at 16:31

Toni H

83
8

Thank you Toni, this is definetly closer to what I'm looking for, however when I run the query I get 1) [Code: 207, SQL State: 42S22] Invalid column name 'BillsPaid'. and BillsSent as well as MoneyId – user11681655 Jun 21 '19 at 17:02
that's going to be your tables/column names - I had assumed you put in dummy table and column names to keep your company data secret... that kind of stuff I won't be able to help with because I don't work with you or have access to your server library of table/column names – Toni H Jun 21 '19 at 18:06
I really appreciate the help, and yes those aren't the actual column names I'm using but I rechecked the ones in my query and they are correctly named however I'm still getting the same error. Ive spent some time googling but cant seem to figure this out. – user11681655 Jun 21 '19 at 18:21
select max(NEEDS TO BE YOUR ACTUAL COLUMN NAME) [Paid] ,max(NEEDS TO BE YOUR ACTUAL COLUMN NAME) [Sent] – Toni H Jun 21 '19 at 18:24
does that help? :) – Toni H Jun 21 '19 at 18:24
No, the column names in those locations are the actual column names I have in the database yet I still am getting the same error. To be more specific I get 7 invalid column name errors. One for NameId, One for moneyId, two for BillsPaid, and three for Billssent – user11681655 Jun 21 '19 at 18:42
as I said previously, that's not going to be anything we can help with since that's specific to your server/database/tables - double check all from clauses and select clauses to make sure that they're all changed to your correct names. Also Google how cross apply works to make sure you're not changing any select clauses from aliases instead of column names by accident. – Toni H Jun 21 '19 at 18:48

Displaying three different columns from three Different Tables in SQL

1 Answers1