Combine results of queries

Question

0.00/5 (No votes)

See more:

Hey hey guys!

Well, I am trying to finish a query that is a little bit complex, but I try in the begging to explain with words and then I share the query with you guys.

-First column - shows me the type of some file(.exe, .pdf, ...)
-Second column - shows me the percentage of that file on the database
-Third column(Here it's where the problem comes) - should show me the size of every type of file and then below the total.

This is the output with this query without giving me the size of each type of files

C#

Filetype | Percentagem | Total (KB)
.pdf        25%         25
.dll        45%         300
                        170

This is my query:

SQL

SELECT Filetype,
((COUNT(Filetype) * 100) / (SELECT COUNT(*) FROM infofile)) AS Percentagem,
NULL AS 'Total (KB)'
FROM infofile
GROUP BY Filetype
UNION ALL
SELECT NULL,
NULL, 
SUM(Filesize)
FROM infofile

What I want to get to is the size of each Filetype in the Total(KB) column.

Thank you for your help. Have a great day

What I have tried:

I've tried to simulate adding another select that actually works but independent from the main query.

SELECT Filetype, SUM(Filesize) AS 'Total(KB)'
FROM infofile
GROUP BY Filetype

Posted 3-May-16 6:24am

Member 12490691

Updated 3-May-16 10:37am

v2

Add a Solution

Comments

CHill60 3-May-16 12:36pm

What is your table schema?

Member 12490691 3-May-16 12:38pm

How can I post a print here?

CHill60 3-May-16 12:39pm

You can't - just list the columns and their types

Member 12490691 3-May-16 12:42pm

What you clearly want?

CHill60 3-May-16 12:45pm

I guessing that your table contains
FileType int,
FileSize int,
... something else.
Also some sample data (5 or 6 rows only) of what is on the table - not the output from your query

Member 12490691 3-May-16 12:51pm

I improved the question with an possible example. By the way every data type is nvarchar

CHill60 3-May-16 18:35pm

Ok. I need to see some sample data from the table [FileType] - NOT your query.
Hint - you do not need to use UNION ... you probably need to look up SQL Windows Functions or OVER functions. If all columns are nvarchar then that is also wrong ... if you are storing numbers then use a number type. nvarchar, varchar, nchar, char should only be used for alphanumeric text

Member 12490691 4-May-16 4:46am

-Filetype - nvarchar - contain extensions files
-Filesize - int

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bensonz · Answer 1 · 2016-05-03T10:37:00

Solution 1

hi you can try this

SQL

SELECT id, code,
    (SELECT SUM(id) AS sumid
     FROM tbl_wtr
     WHERE (accid = newtb.id)) AS sumid
FROM
    (SELECT id, code
     FROM tbl_stacc) AS newtb

Posted 3-May-16 10:37am

bensonz

Updated 3-May-16 10:47am

v2

Comments

Member 12490691 3-May-16 17:05pm

Hey. Thank you for your solution. Your query does not apply with the query that I am trying to build.

CHill60 4-May-16 4:39am

Do the answers on this post help - Column total at the end of SQL pivot query[^]

Member 12490691 4-May-16 4:54am

What I have in my query is actually working but instead of using that 'NULL AS 'TOTAL(KB)' I would like to use a SELECT to query me what I want in that column.

This is the Select that I want to include in my query
SELECT Filetype, SUM(Filesize) AS 'Total(KB)'
FROM infofile
GROUP BY Filetype