Click here to Skip to main content
15,032,967 members
Please Sign up or sign in to vote.
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
Updated 3-May-16 10:37am
v2
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

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
   
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900